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

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

> That's basically it, but remember that the rowid becomes part of the index
> key if the index is unique.

I guess you meant to say that the rowid becomes part of the index if it is *non* unique. For unique indexes there is no need to disambiguate multiple values. Or am I missing something?

I do have to say that I find the sentence "For a nonunique index, the rowid is included in the key in sorted order" from the documentation to be a bit awkward: On one hand the ROWID is always stored in the index - close to the key values for obvious reasons (the docs do not differentiate both index types when describing leaf block storage). On the other hand the ROWID does not really become part of the key because on schema level you do not see it even though Oracle might use it when including a ROWID based condition in the WHERE clause of a query (which it could also do for unique indexes).

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref966

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

Right. Thank you for the correction!

For the fun of it and to make things a bit more complicated: things are a tad different when also considering secondary indexes on index organized tables:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#sthref2433

:-)

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/