Re: Function Based Index

From: Purav Chovatia <puravc_at_gmail.com>
Date: Mon, 10 Sep 2012 01:11:52 +0530
Message-ID: <CADrzpjHa+Y7OQ8Q=zx9xxa-nYZOnLukULDu3eif=SnCgfDXr6g_at_mail.gmail.com>

Thanks Stefano.
But yesterday, explain plan was showing a plan that used the FBI. So I thought that should have got picked.

Further update, just tried the explain plan again and it shows a plan with FTS i.e. does not use FBI. That is surprising. Also tried set autotrace traceonly explain and that too showed a plan with FTS i.e. not using the FBI.
Gathered statistics again, this time with for all hidden columns size 1 and so histogram got generated (not that it was not existing previously but just tried be sure of). But the plan remained same.

Thanks.
On Mon, Sep 10, 2012 at 12:19 AM, Stefano Cislaghi <s.cislaghi_at_gmail.com>wrote:

> 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-l

Received on Sun Sep 09 2012 - 14:41:52 CDT