Re: Odd behavior of function-based index after DB upgrade

From: Rakesh Tikku <rakesh.tikku_at_gmail.com>
Date: Wed, 5 Oct 2011 22:47:52 -0700
Message-ID: <CAE5B=WOZd4F7+DfRPX6PbXZvaD7_b=AS4yKMtDSyeQedy2vHsw_at_mail.gmail.com>

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_at_xerox.com> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Oct 06 2011 - 00:47:52 CDT