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

On 03/06/2010 05:02 PM, ErikYkema wrote:
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/

"Robert Klemme" <shortcutter (AT) googlemail (DOT) com> wrote


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

On Sat, 06 Mar 2010 16:54:17 +0000, Jonathan Lewis wrote:

I believe that the algorithm is commonly known as binary search and
implies that the array which is searched is ordered, which is true for
the rowids with the same key, within the same index.



--
http://mgogala.freehostia.com

On 6 mrt, 17:02, ErikYkema <erik.yk... (AT) gmail (DOT) com> wrote:
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

On 06.03.2010 17:54, Jonathan Lewis wrote:
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/B....htm#sthref966

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/B...htm#sthref2433

:-)

Kind regards

robert


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