Re: Function Based Index
Date: Sun, 9 Sep 2012 20:49:40 +0200
Message-ID: <CAFsgGrwNVkrK7_2NwDFLaiMRGS+ekiYWtVWAxnZGEcfPQKEY=A_at_mail.gmail.com>
Hi,
Try to force the use of the FBI with an hint and compare plans. Maybe the use of FBI produces a worser plan and CBO avoids to use it.
Ste
On 9 Sep 2012 20:36, "Purav Chovatia" <puravc_at_gmail.com> wrote:
> Hello everybody,
> I have a function based index but the CBO is not using it. The DML that I
> expect to have a plan with index range scan is doing a FTS. Its a simple
> DML that deletes 1000 rows at a time in a loop and is based on the column
> on which the FBI is created. The DML is executed as a part of a batch job
> at eod.
>
> 'Explain plan for' for the DML with the same values, shows an index range
> scan as expected. hence 10053 would also show the same, I guess.
>
> This is 10205 on solaris x86 and optimizer statistics are gathered for the
> table, index and the virtual/hidden column.
>
> How do I find out why is the CBO not using the FBI.
>
> Surprisingly, neither AWR nor statspack report show the DML which should
> have appeared because another DML that has a much lesser elapsed time or
> has comparatively less number of executions appears in the report.
> Yesterday I enabled a 10046 trace and then confirmed via tkprof that it is
> indeed doing a FTS and not index range scan.
>
> Thanks.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Sep 09 2012 - 13:49:40 CDT