Oracle Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle Indexes

  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Oracle Indexes

    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

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    make contact with the author and ask him to explain

    www.jlcomp.demon.co.uk
    Able was I ere I saw Elba

  3. #3
    Join Date
    Nov 2005
    Location
    Indianapolis
    Posts
    24

    This might help...

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width