Re: Odd behavior of function-based index after DB upgrade
- From: Rakesh Tikku <rakesh.tikku@xxxxxxxxx>
- To: Amir.Hameed@xxxxxxxxx
- Date: Wed, 5 Oct 2011 22:47:52 -0700
Amir,
While you are getting a 10053 trace, you can try one more thing.
gather stats on the table with method_opt=> 'FOR ALL HIDDEN COLUMNS'
function indexes are internally implemented by adding a hidden column to the
table that stores the value with the function applied. I have seen missing
stats on this column causing function index to not to be picked up.
Rakesh
Managing Principal
DB Perf Inc.
On Wed, Oct 5, 2011 at 6:43 PM, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
> VOUCHER_NO contains 7 digits.
> SYSTEM has 24 distinct values
>
> This table contains around 10,749,644 rows and the function-based index has
> 10,442,518 distinct values. So, the index is pretty selective. Without
> forcing the index via the RULE hint, the job ran for over 8 hours and had to
> be killed. With the rule hint, it ran in 15 minutes.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
