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

From: ErikYkema <erik.ykema_at_gmail.com>
Date: Sat, 6 Mar 2010 13:04:56 -0800 (PST)
Message-ID: <0cab16c8-312e-4637-8ebb-dde5c5d732fd_at_g10g2000yqh.googlegroups.com>



On 6 mrt, 17:02, ErikYkema <erik.yk..._at_gmail.com> 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?
>
> I hope the sample and question are clear,
> Thanks and best regards,
> Erik Ykema

Thanks to all,
I overlooked the very very obvious - I wondered the ROWID to have some kind of backdoor "directly" into the additional index, i.s.o. a general index look up based on the old indexed column values. Regards, Erik Received on Sat Mar 06 2010 - 15:04:56 CST

Original text of this message