Oracle Indexes

[khemani]

Hi,

I was reading Jonathan Lewis's book "Practical Oracle8i" and encountered a couple of roadblocs, which I would highly appreciate if someone could shed some light on:

In Oracle indexes, the branch block consists of the first entries of
the lower level blocks (branch or leaf) along with block addresses.
Jonathan Lewis states that the first key in a branch block isn't stored to save a few bytes. What this means is that the branch block has the block address of the first lower level block that it encompasses, but does not include the first entry in that block to go with the block address. My question is that if a random value is to be searched through the index that the index indicates lies in the first leaf block (and say there are only two levels in the index), then whether or not that value actually exists in the block will only be determined by reading the leaf block since the branch block does not have the first entry in it. Won't this result in more I/O, which could be saved if the the first entry of the leaf block were included in the branch block?

Secondly, I read the following paragraph in the book:

In a non-unique index, the rowid is actually added at the end of the
index columns, and appears as part of the index for the purposes of
Oracle's internal workings. This can mean that certain types of
non-unique indexes (typically ones with low cardinality) can suffer
from rather poor compression rates in the branch blocks, and can be a few percentage points larger than you might otherwise expect.

First of all, isn't the rowid part of all indexes, not just non-unique
indexes. And could you also explain what internal workings Mr. Lewis is talking about? Secondly, in a non-unique index, is the rowid of each record that has a particular value in the non-unique index included after that value in the index? Thirdly, why would non-unique indexes with low cardinality (low average number of records returned on an equality selection condition, if I am not mistaken) suffer from poor compression rates in the branch blocks and what type of compression are we talking about?

I would highly appreciate if someone clarified these issues.

Karim

[robertbalmer]

make contact with the author and ask him to explain

www.jlcomp.demon.co.uk

[wdfloyd]

I read something on asktom about this, http://asktom.oracle.com/pls/ask/f?p...:7641143144618

The rowid is included in the index whether it is unique or not. The only difference is when it considered part of the key.