Re: Performance problem with deleteing large number of rows from a table

From: Richard Foote <richard.foote_at_bigpond.nospam.com>
Date: Sat, 12 Aug 2006 14:49:20 GMT
Message-ID: <QTlDg.11004$rP1.1210@news-server.bigpond.net.au>

Hi Robert

Comments embedded.

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:4k3jg1Fa6l92U1_at_individual.net...
> On 11.08.2006 15:54, Richard Foote wrote:

>> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message 
>> news:4k0fhpFa0m24U1_at_individual.net...
>>> On 10.08.2006 12:01, sybrandb wrote:
>>>>> 1. Table does not have indexes, PK etc (This should help deletion --
>>>>> Hope I am right)
>>> I don't think so.  Without indexes Oracle has to do a table scan to find 
>>> all records that you want to delete.  My guess would be that if you 
>>> typically delete 10% of your 500,000 rows a table scan is way slower 
>>> than a deletion based on an index lookup (assuming a proper index in 
>>> place).
>>>
>>
>> Hi Robert
>>
>> I question claims such as "if you *typically* delete 10% of your 500,000 
>> rows a table scan is way slower than a deletion based on an index loop". 
>> It may indeed be correct but typically it's unlikely to be the case at 
>> all.
>>
>> See 
>> http://groups.google.com/group/comp.databases.oracle.server/msg/96b20de9b49d2d6c 
>> for an explanation on why such claims are dangerous and generally 
>> incorrect.
>

> I stand corrected. That's an interesting read! Thanks for taking the
> time to write it up!

> This leads me to further questions / remarks:

> 1. If the table is index organized am I right to assume that this will
> dramatically change the calculation? Using your example we would get rid
> of 1,000,000 LIO to table blocks. In that case the index access seems to
> be always superior.

Well yes and no. Yes you no longer have to visit a heap table structure as such (assuming you have no overflow area defined), however assuming the table data and volume is the same, you'll still have the 1,000,000 blocks. They'll just be part of the leaf index structure. And you still need the various layers of branch blocks sitting above the leaf blocks so a FTS (or a full index scan as is kinda the case of a IOT) will actually be more expensive as a result of having to also access these additional branch blocks during this process.

Assuming you could access these 10% through the PK, then obviously a range scan of just the 10% of the index structure would be preferable to a full index scan.

But assuming you could only access these say 10% of data through a secondary index, yes the calculation would change somewhat as secondary indexes on IOT tables are a somewhat different beast to their standard index cousins. The calculation would need to take into consideration how stale are the "guess" block addresses of the secondary index. The more stale, the more direct probes fail and the more PK accesses are required to retrieve the necessary data.

So no, the index access of a secondary index would not necessarily be better than a full index scan.

> 2. There seem to be numerous factors that make this whole calculation even
> more complex (you said, you keep it simple :-)). Things I can think of:
> type of index, partitioning (index local or not, partition pruning
> possible or not...) and a whole lot others as well.

Correct. That's why we need people like Jonathan Lewis to write articles and books on this sort of stuff !!

> 3. You mention parallelism as a major factor. Does it play in favor of
> one or the other? I imagine a table scan might be easier to partition.

Depending of database version and depending on table and index structure it could effect both.

However, in my "simple" example on a non partitioned table, the FTS could be broken up into various parallel processes making it potentially even more appealing to the CBO.

>
> 4. Do you have any empirical data at hand?

You may find the following presentation of use as it attempts to both dispel some of these myths about indexes and tries to empower the user to gather their own sets of empirical data:
http://www.miracleas.dk/images/upload/Docs/Richard%20Foote.pdf

Cheers

Richard Received on Sat Aug 12 2006 - 09:49:20 CDT