Re: How Oracle find rows to delete in an index

• Post for FREE via your newsreader at post.usenet.com ****

Hi Bruno

"Bruno Jargot" <see_at_reply-to.invalid> wrote in message news:1giol2m.e8rfx04hambgN%see_at_reply-to.invalid...

> So it could explain my problem :
>
> I've a very big table with 4 indexes :
> - 2 of them are discrimants
> - 2 are not discriminants

What do you mean for discriminants? Many distinct keys, i.e. good selectivity?

> delete on these table take long time. A lot more time than on another
> table with similar volume but with only 2 (discriminants) indexes.

How much is "a lot more time"?

> I don't think that's the two more indexes are enough to justify the
> difference of time. But the fact that the 2 indexes are not
> discriminants could lead Oracle to take a lot of time to locate the row
> to delete on these index.

For non-unique indexes Oracle appends the ROWID to the index key. Therefore, when Oracle knows the index key and the ROWID (which is the case since Oracle has already found it by scanning the first index, T_PK in my previous example) the access is optimal as well. This is true even if you have an index with a single distinct value, i.e. the delete operation needs few I/O (usually from 1 to 4). Therefore, the selectivity of an index doesn't result in bad performance during delete operations.

> I will try to do a trace tomorrow but could my explanation be correct ?

My guess is, that the performance difference is caused by the two additional indexes.

Chris

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

• Usenet.com - The #1 Usenet Newsgroup Service on The Planet! *** http://www.usenet.com Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=