A brief analysis of jumping index (skip Scan index)

In Oracle9i, there is a new feature: Skipping index (skip Scan index). When a table has a composite index, and there are other columns in the query except the first column in the index as criteria, and the optimizer mode is CBO, the query plan is likely to use the SS. In addition, you can use the Hint INDEX_SS (CBO) to enforce the use of SS.

Example:

Sql> CREATE TABLE Test1 (a number, B char (a), C VARCHAR2 (10));

Table created.

Sql> CREATE index test_idx1 on Test1 (A, b);

Index created.

Sql> set Autotrace on

Sql> Select/*+INDEX_SS (test1 test_idx1) */* from Test1 a

2 where B = ' a ';

No rows selected

Execution Plan

0 SELECT STATEMENT optimizer=choose (cost=2 card=1 bytes=32)

1 0 TABLE ACCESS (by INDEX ROWID) of ' TEST1 ' (cost=2 card=1 bytes=32)

2 1 INDEX (SKIP SCAN) of ' test_idx1 ' (non-unique)
However, the SS is not always used in any case. In Oracle's official documentation, in addition to the need for CBO and analysis of tables, it is important to ensure that the first column distinct value is very small. This section is an explanation of the SS as extracted from the official document:

Index skip scans improve index scans by Nonprefix columns since it be often faster to scan index blocks than scanning tabl e data blocks.

In this case a composite index are split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence It is now possible to use the index even if the leading column isn't used in a WHERE clause.

Oracle has not published any more internal technical details about SS. But note the above sentence: in the casea composite the index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. That is, the oralce will logically divide the composite index into multiple sub indexes. As you can understand, Oracle divides the indexes logically into a.num_distinct, each time a child index is scanned. Therefore the index scan cost of SS is a.num_distinct.

Let's do some experiments to see where Oracle uses SS.

First of all to ensure the use of SS of several necessary conditions:

· Optimizer for the CBO

· The relevant table should have the correct statistic data

· Oracle DB version is above 9i

Here is a special condition to use SS: the distinct num in the first column is small enough. How small to what extent?

Or take the above table as an example (omit the middle of the troublesome step, take two critical values to do the experiment):

Take the first column distinct number 37:

sql> truncate TABLE test1;

Table truncated.

Sql> begin

2 for I in 1..100000 loop

3 INSERT into test1 values (mod (i,37), To_char (i), to_char (i));

4 End Loop;p;

5 commit;

6 end;

7/

Pl/sql procedure successfully completed.

sql> Analyze table test1 compute statistics;

Table analyzed.

Sql> set Autotrace on explain

Sql> SELECT * from Test1

2 where B = ' 500 ';

A B C

---------- ---------- ----------

19 500 500

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT optimizer=choose (cost=37 card=1 bytes=17)

1 0 TABLE ACCESS (full) ' TEST1 ' (cost=37 card=1 bytes=17)