Re: how does update on table find other index blocks to maintain
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Sat, 06 Mar 2010 17:33:24 +0100
Message-ID: <7vfeekF1s7U1_at_mid.individual.net>
On 03/06/2010 05:02 PM, ErikYkema wrote:
> 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?
Date: Sat, 06 Mar 2010 17:33:24 +0100
Message-ID: <7vfeekF1s7U1_at_mid.individual.net>
On 03/06/2010 05:02 PM, ErikYkema wrote:
> 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?
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?
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Sat Mar 06 2010 - 10:33:24 CST