Re: How Oracle find rows to delete in an index

On Tue, 17 Aug 2004 21:13:48 +0200, see_at_reply-to.invalid (Bruno Jargot) wrote:

>Hi Christian,
>
>Christian Antognini wrote:
>
>> "Bruno Jargot" wrote in message
>
>> > Will Oracle do a range scan in the index I2 after finding the value of
>> > C2 in the table ?
>>
>> This is the only way to locate an index entry... a simple tests confirm this
>> sequence as well...
>
>Thanks for the explanation.
>
>So it could explain my problem :
>
>I've a very big table with 4 indexes :
>- 2 of them are discrimants
>- 2 are not discriminants
>
>delete on these table take long time. A lot more time than on another
>table with similar volume but with only 2 (discriminants) indexes.
>
>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.
>
>I will try to do a trace tomorrow but could my explanation be correct ?

Not likely. Only one index will be used to determine the records to be deleted. However potentially *all* indexes need to be maintained!

--
Sybrand Bakker, Senior Oracle DBA