Small test on Oracle 9i Jump Index Scan (index Skip Scan)

oracle| Index

In oracle9i we know that we can use the Jump Index Scan (index Skip Scan). However, there are some limitations to the use of jumping index scans.

From Oracle's documentation we can find this:

Index Skip Scans
Index skip scans improve index scans by Nonprefix columns.
Often, scanning index blocks is faster than scanning table data blocks.
Skip scanning lets a composite index is split logically into smaller subindexes.
In skip scanning, the initial column of the composite index isn't specified in query.
In the other words, it is skipped.

The number of logical subindexes is determined from the number of distinct values in the initial column.
Skip scanning is advantageous if there are few distinct values in the leading column of the composite
Index and many distinct values in the Nonleading key of the index.

You can also say that the optimizer determines whether to use skip Scan based on the number of unique values in the leading column in the index (the first column indexed).

Let's do a test first:

sql> CREATE TABLE Test as
2 SELECT rownum a,rownum-1 B, ROWNUM-2 c,rownum-3 d,rownum-4 E
3 from All_objects
4/

Sql> SELECT DISTINCT COUNT (a) from test;

COUNT (A)
----------
28251

Table has been created.

Sql>
Sql> CREATE INDEX test_idx on test (A,B,C)
2/

The index has been created.

sql> ANALYZE TABLE Test COMPUTE STATISTICS
2 for TABLE
3 for all INDEXES
4 for all INDEXED COLUMNS
5/

The table is parsed.

Sql> SET autotrace Traceonly explain
Sql> SELECT * FROM Test WHERE b = 99
2/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=36 card=1 bytes=26)
1 0 TABLE ACCESS (full) ' TEST ' (cost=36 card=1 bytes=26)

--This is where the CBO chooses a full table scan.

--we went on to do another test:

sql> drop table test;

The table has been discarded.

sql> CREATE TABLE Test
2 AS
3 SELECT DECODE (MOD (rownum,2), 0, ' 1 ', ' 2 ') A,
4 ROWNUM-1 B,
5 ROWNUM-2 C,
6 ROWNUM-3 D,
7 ROWNUM-4 E
8 from All_objects
9/

Table has been created.

Sql> set Autotrace off
Sql> SELECT distinct a from test;

A
--
1
2

--A column has only two unique values

Sql> CREATE INDEX test_idx on test (A,B,C)
2/

The index has been created.


sql> ANALYZE TABLE Test COMPUTE STATISTICS
2 for TABLE
3 for all INDEXES
4 for all INDEXED COLUMNS
5/

The table is parsed.

Sql> Set Autotrace traceonly explain
Sql> SELECT * FROM Test WHERE b = 99
2/

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT optimizer=choose (cost=4 card=1 bytes=24)
1 0 TABLE ACCESS (by INDEX ROWID) of ' TEST ' (cost=4 card=1 bytes=24)
2 1 INDEX (SKIP SCAN) of ' Test_idx ' (non-unique) (cost=3 card=1)



The Oracle Optimizer (here refers to the CBO) can apply the index SKIP scans to queries at least a few conditions:

The 1 optimizer considers it appropriate.
The number of unique values for the leading columns in the 2 index satisfies certain conditions.
3 optimizer to know the value distribution of leading columns (through analysis/statistics)
4 Appropriate SQL statements
......


For more information, please refer to:

http://www.itpub.net/showthread.php?threadid=85948

http://www.cnoug.org/bin/ut/topic_show.cgi?id=608&h=1&bpg=1&age=100

http://www.itpub.net/showthread.php?s=&postid=985602#post985602

Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2)
Part number a96533-02

Thank you to the experts who participated in the discussion.