how does update on table find other index blocks to maintain
Date: Sat, 6 Mar 2010 08:02:45 -0800 (PST)
Message-ID: <9fb9cf5d-e212-4b18-9419-4141a06ec383_at_19g2000yqu.googlegroups.com>
Hi,
Just for general understanding, I have a question, related to how the rowid to index block lookup is done.
This question was spawned by a 10046 trace that was run through the TraceAnalyzer (from Metalink, great tool) that showed me serious reads on an index that was not part of the access path from the optimizer plan, and I assume that this was to update this secondary index as a result of the update on the table.
Consider this example: I have 2 indexes on a table, one the primary key PK, and another index, say A1 on an other field, for better access paths. (For this sample, the sanity of the design is another issue.) Now my update is done on this table using a data access path with PK. However in my trace report I also see the A1 index being read. That I understand, as the field's values are being changed and need to be reflected in the A1 index.
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?
I hope the sample and question are clear,
Thanks and best regards,
Erik Ykema
Received on Sat Mar 06 2010 - 10:02:45 CST