| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Composite indices and skip scanning
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 !
Cheers
Richard
Oracle 9.2.0.2.0=20
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?
 |
 |