Index Skip Scan

Prior to Oracle 9i, it was not possible to perform an Index Scan (Range or Unique) on a concatenated index unless the WHERE clause included a predicate on the leading column(s) of the index. Index Skip Scan allows Oracle to use an index when the first column is not supplied in the WHERE clause.

Consider the index:

A Skip Scan does not require you to re-write the query; it doesn't even perform a rewrite internally like some other features. It simply scans the index sub-trees for each of the possible values of the leading column.

Consider what would happen if there were 5000 different values of deptno. Instead of 3 sub-tree scans, the query would perform 5000 of them. Clearly this would be much less efficient; it would be far better in this case to create an index with job as the leading column. If statistics have been gathered correctly, the Cost Based Optimizer will know the cardinality of the leading column, and be able to decide for itself whether an Index Skip Scan is appropriate.

If you have a query that could scan on the second and subsequent columns of the index, but is not using an Index Skip Scan:

Still not using a Skip Scan?

Did it start using the Skip Scan when you added the hint? If so, Oracle thinks that the Skip Scan will be inefficient; that's why it is not used without the hint. Benchmark the query with and without Skip Scan to make sure you have the best plan.

©Copyright 2003