Function Based Index
From: Purav Chovatia <puravc_at_gmail.com>
Date: Mon, 10 Sep 2012 00:05:25 +0530
Message-ID: <CADrzpjGbETZ7OerqEaYngTChihB2Xo5QqzK8v5aA+ip4GpkRJQ_at_mail.gmail.com>
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.
Date: Mon, 10 Sep 2012 00:05:25 +0530
Message-ID: <CADrzpjGbETZ7OerqEaYngTChihB2Xo5QqzK8v5aA+ip4GpkRJQ_at_mail.gmail.com>
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-lReceived on Sun Sep 09 2012 - 13:35:25 CDT