Re: How Oracle find rows to delete in an index

news_at_lirano.net (Bruno Jargot) wrote:
> Hello,
>
> I hope it's not a stupid question :
>
> How do Oracle find the row to delete in an index ?
>
> Example :
> A table T with 2 indexes I1 on T(C1) and I2 on T(C2)
>
> Suppose we execute the following request :
> delete from T where C1 = 'foobar';
>
> With the index I1, Oracle will find the rowid of the line to delete.
>
> It already knows the row to delete in the index I1 and it knows the
> rowid of the row in the table.
> But how will Oracle find the corresponding row to delete in the index I2
> ?
>
> Will Oracle do a range scan in the index I2 after finding the value of
> C2 in the table ?

My understanding is that all degenerate entries in ordinary non-unique indices are stored in order of RowID, so it can effectively do a "unique" index lookup into I2 using the concatenated values of C2 and the RowID, rather than a range scan of all entries with the same value for C2. But I could be wrong.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB