Index skip scan

The index skip scan is a new execution plan in Oracle9i whereby an Oracle query can bypass the leading-edge of a concatenated index and access the inside keys of a multi-values index.

For example, consider the following concatenated index:

create index sex_emp_id on emp (sex, emp_id);

Prior to Oracle9i, this index could only be used with both sex and emp_id were present in the SQL query, or when the sex column was specified.  The following query would not be able to use the concatenated index:

select emp_id from emp where emp_id = 123;

The Oracle9i skip scan execution plan allows for the concatenated index to be used, even though sex is not specified in the SQL query.  This feature promises that there is no need to provide a second index on the emp_id column.  Oracle acknowledges that the index skip scan is not as fast as a direct index lookup, but states that the index skip scan is faster than a full-table scan (otherwise why would oracle introduce such a feature).

What Oracles does not mention is that the cardinality of the leading column has a direct impact on the speed of the index skip scan.  In our example, the first column, sex has two records (‘F’, and ‘M’).

While Oracle does not publish the internals of the index skip scan, we can infer from the execution plan that Oracle is internally generating multiple queries, thereby satisfying the query with multiple sub-queries:

SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=5)

   0 SORT (AGGREGATE)

        1 INDEX (SKIP SCAN) OF 'SEX_EMP_ID' (NON-UNIQUE)

Internally, Oracle9i is probably generating two queries and joining the resulting Row ID lists:

select emp_name from emp_where sex = ‘F’ and emp_id = 123

UNION

select emp_name from emp_where sex = ‘M’ and emp_id = 123;

The implications of using the index skip scan are clear:

• Oracle skip scan execution plan performance will decrease according to the number of unique values in the high order key.   If the leading column were “state” with 50 values, Oracle would be issuing 50 index probes to retrieve the result set.

 

By Donald K. Burleson

Article courtesy: http://www.praetoriate.com/oracle_tips_skip_scan.htm

 

Comments from the author: If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link: http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm