Re: Function Based Index

You mention that the DELETE statement is supposed to delete a very small number of rows (i.e. "say just over 200k")?

200k rows is actually a very large index range scan, and it is assumptions like this where the "general rule of thumb" that the optimizer chooses to use an index when scanning less than 5% of the rows in a table causes trouble. Or any percentage (i.e. 1%, 5%, 10%, 15%, etc) that people pick out of thin air.

The 5% thing is not part of the Oracle optimizer at all. Instead, it is simply an observation, a guess, that someone has made and then spread in a presentation. It is a number plucked out of ... thin air, and when a table is relatively small, it seems accurate enough.

However, what the optimizer is actually doing is calculating the expected "cost" (in I/O and CPU) of each execution plan, and ratios like 1% or 5% have nothing to do with that formula. Instead, the number of blocks under the high-water mark of the table, the number of distinct keys in the index, the cluster factor of the index entries to the table rows -- those are the variables being considered. The effect of that calculation might seem like there is a simple ratio at work, but the cause involves using the gathered cost-based optimizer statistics mentioned in the previous sentence, plus a few others.

So, the fact is that an enormous index range scan like 200k rows is probably calculating out to something like 500k or more logical I/Os involving several thousand physical I/Os, an enormous amount of work, never mind the modifications to both the table and index and the need for consistent reads on both table and index, while the execution plan involving a FULL table scan is likely costing out significantly less, partly due to less need for consistent reads from the modifications. DML is a lot different than queries.

So, I am joining this thread late and have not seen your original post, but does a SELECT COUNT(*) on the table with the same WHERE clause use the FBI? It might, because SELECT is not doing as much work as DELETE. Have you tried forcing the use of the FBI with a hint, just for testing purposes, in SQL*Plus using SET TIMING ON and SET AUTOTRACE ON, just to compare the elapsed time along with the "consistent gets", "db block changes" against the plan chosen by the optimizer. I think you might find that the 200k index range scan really is way too inefficient, and that the optimizer is actually correct in choosing not to use the FBI.

If you've already done these comparative tests and posted the results, then my apologies for bringing it up again...

Hope this helps!

Thanks!

-Tim

On 9/11/2012 2:36 AM, Purav Chovatia wrote:
> KEEP pool is configured for the instance. And the table's buffer_pool
> attribute is set to KEEP.
> Also, we use ALTER TABLE TABLE_NAME CACHE;
> Could that be the reason for either of the observations that I have made?
>
> The table has approx.14million rows. There are 3 columns and 1 of those is
> a DateTime column which is updated with sysdate value if the record exists
> else a new record is inserted with sysdate value in the DateTime column.
> Most of the times, the record exists and hence it results in an update.
>
> FBI is on the DateTime column.
>
> DML (expected to use the FBI): delete from <table_name> where
> trunc(datetime) < (trunc(sysdate) - 31) and rownum < 1000;
>
> Table contains data for last 31 days. The number of rows to be deleted is
> very small, say just over 200k.
>
> Pls let me know if I should provide any other info.
>
> Thanks
>
> On Mon, Sep 10, 2012 at 9:28 PM, Stefano Cislaghi <s.cislaghi_at_gmail.com>wrote:
>
>> I agree. Anyway if you do not provide us more information and the
>> query anything we might say are only theory and guessworks.
>>
>> Ste
>>
>> On 10 September 2012 17:48, Allen, Brandon <Brandon.Allen_at_oneneck.com>
>> wrote:
>>> Maybe the CBO is choosing not to use the index because the percentage of
>> blocks it thinks you will need is too high? I don't know exactly how the
>> CBO calculates it, but the general rule of thumb is to only use an index if
>> you're going to read less than 5% of the table's blocks. If the values in
>> your look up column are skewed, you could try running the query with an
>> unpopular value such that Oracle will be more likely to use an index (make
>> sure you flush the old plans out of the pool first), and then, if it does
>> load the plan with the index, you can create a sql plan baseline to make it
>> stick with that plan for future executions - that is assuming you're on
>> 11g; create a stored outline if you're on 10g.
>>> Regards,
>>> Brandon
>>>
>>>
>>>
>>> ________________________________
>>>
>>> Privileged/Confidential Information may be contained in this message or
>> attachments hereto. Please advise immediately if you or your employer do
>> not consent to Internet email for messages of this kind. Opinions,
>> conclusions and other information in this message that do not relate to the
>> official business of this company shall be understood as neither given nor
>> endorsed by it.
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>
>>
>> --
>> http://www.stefanocislaghi.eu
>>
>> The SQLServerAgent service depends on the MSSQLServer service, which
>> has failed due to the following error: The operation completed
>> successfully.

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