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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Mon, 14 Aug 2006 11:47:45 +0200
Message-ID: <4kav61Fbd20tU1@individual.net>

Again, thanks for taking the time!

On 12.08.2006 16:49, Richard Foote wrote:

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

*That* was the case I had in mind. Sorry for not speaking it out loud.

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

Yeah.

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

... and folks like you who take the time to explain things a bit here.

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

Ok.

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

Thanks! I'll look into it. If only I had more time to thoroughly deal with these things...

Kind regards

        robert Received on Mon Aug 14 2006 - 04:47:45 CDT