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
Consider the index:
CREATE UNIQUE INDEX emp_i1 ON emp (deptno, job); SELECT * FROM emp WHERE job = 'CLERK';
Say there are only 3 different values for
deptno: (1, 2, and 3). The query above is equivalent to the following query which techically could use an index without a Skip Scan:
SELECT * FROM emp WHERE deptno IN (1,2,3) AND job = 'CLERK';
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:
INDEX_SShint to the SQL. This will instruct the optimizer to use an Index Skip Scan.
INDEX SKIP SCAN.
Still not using a Skip Scan?
WHEREclause on the second column of the index. Skip Scan can only skip the first column, not two or more.
WHEREclauses on the first and third columns, at best you will get a Range Scan on the first column.
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.