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@xxxxxxxxx> 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. > > > -- > //www.freelists.org/webpage/oracle-l > > > -- //www.freelists.org/webpage/oracle-l