Re: how does update on table find other index blocks to maintain

"Robert Klemme" <shortcutter_at_googlemail.com> wrote in message news:7vfeekF1s7U1_at_mid.individual.net...
> On 03/06/2010 05:02 PM, ErikYkema wrote:
>> Hi,
>> Question: how does the oracle database engine effectively do the
>> reverse lookup of the rowid that was given by the index PK, to the
>> block of the index A1 that holds the entry for that rowid?
>
> The most natural thing would be to use the old value of the field(s) that
> A1 indexes and do a regular index lookup. If the index is non unique
> ROWIDS of all entries for that key need to be checked additionally. What
> other ways could there be that would be equally efficient?
>

Robert,

That's basically it, but remember that the rowid becomes part of the index key if the index is unique. So we have (col1, col2) as our index, but internally
the index is (col1, col2, rowid). This means that index entries for the same
key value (as we see it) appear in the index ordered by rowid. So the same "binary chop" approach that Oracle uses to find a unique key allows it to find
the specific rowid within the list of non-unique values very efficiently - it doesn't
have to "check all entries" for the key.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com