Re: Composite indices and skip scanning
- From: "Richard Foote" <richard.foote@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 15 Apr 2004 23:54:52 +1000
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
----- Original Message -----
From: "Boyle, Christopher J." <Christopher.J.Boyle@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, April 15, 2004 11:11 PM
Subject: Composite indices and skip scanning
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?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
