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:

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:

• Ensure you are using Oracle 9i or later. Index Skip scans are not available on v8i and earlier releases.
• Try adding an INDEX_SS hint to the SQL. This will instruct the optimizer to use an Index Skip Scan.
• Run the query through Explain Plan and check for a line including INDEX SKIP SCAN.

Still not using a Skip Scan?

• You must have a WHERE clause on the second column of the index. Skip Scan can only skip the first column, not two or more.
• Skip Scan will not skip a second or subsequent index column. eg. If you have WHERE clauses on the first and third columns, at best you will get a Range Scan on the first column.
• Work through all of the other reasons why an indexe may be ignored. These reasons all apply to Skip Scans the same as for Unique and Range Scans.

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.