Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Composite indices and skip scanning

Re: Composite indices and skip scanning

From: Richard Foote <>
Date: Thu, 15 Apr 2004 23:54:52 +1000
Message-ID: <009201c422f1$3a4723e0$0100000a@FOOTE>

Hi Christopher,

No, there's nothing unique about a unique index (I can't believe I just said that !!)

However, it all comes down to the relative cost, as it always does with the CBO. The cost of using the Index Skip Scan option is somewhat dependent on the cardinality of the leading column(s). If the cardinality is too high and in the worst case scenario this would be a value greater than the number of leaf blocks (assuming even data distribution) then the CBO calculates that all leaf blocks have to be visited anyways and this is factored into the cost of the execution plan. This is because the leading column changes for each leaf block (on average) meaning that the required second column could exist in any of these blocks. If the cardinality of the leading columns is somewhat less than the number of leaf blocks, then the CBO can determine the probable number of leaf blocks that could be skipped, making the index less costly to navigate and more attractive to the CBO.

Obviously, only having the second column indexed is hunky dory to the CBO which does therefore suggest the cardinality of the second column is fine (is high enough) but the cardinality of the leading column is just too high for the ISS to be effective.

Hope this makes some kinda sense !




I have an intersection table composed of PK_TABLE_A, PK_TABLE_B which is being used to handle a M:M relationship between A and B. There is a unique constraint on the combination of the two entries and a corresponding index with A as the leading value. When I was trying to access table A using the PK from table B I expected it to use that index because of the 9i skip scan feature that allows for a non leading index member to still utilize the index. What I got was a FTS until I added a separate index on PK_TABLE_B only. Is there something with a unique index that prevents skip scanning? Or do I have too much blood in my caffeine and am mis-rembering how the indices should work?

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Apr 15 2004 - 08:55:49 CDT

Original text of this message