In Oracle9i, we know that you can use the Skip Scan. However, the case where the skip index scan can be used is actually restricted.
From Oracle's documentation we can find this:
Index Skip ScansIndex 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 be split logically into smaller subindexes. In skip scanning, the initial column of the composite index is NOT Specified in The Query. in Other Words, IT is Skipped.
The number of logical subindexes is determined by 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 compositeindex and many distinct values in the nonleading key of the index.
It can also be said that the optimizer determines whether the SKIP Scan is used in accordance with the number of the unique values of the leading list (indexed first column) in the index.
We first do a test:
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
The table has been created.
SQL> SQL> CREATE INDEX TEST_IDX ON TEST (A, B, C) 2 /
Index has been created.
SQL> Analyze Table Test Compute Statistics 2 for Table 3 for All Indexes 4 for All Indexed Column 5 /
The table has been analyzed.
SQL> Set Autotrace Traceonly Explainsql> 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) of 'Test' (COST = 36 Card = 1 BYtes = 26)
- Visible here CBO selected a full table scan.
- We will then 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 Offsql> Select Distinct A from Test;
A - 12 - A column only two unique values
SQL> CREATE INDEX TEST_IDX ON TEST (A, B, C) 2 /
Index has been created.
SQL> Analyze Table Test Compute Statistics 2 for Table 3 for All Indexes 4 for All Indexed Column 5 /
The table has been analyzed.
SQL> Set Autotrace Traceonly Explainsql> 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' (COST = 3 CARD = 1)
Oracle's optimizer (herein, CBO) can apply at least a few conditions for querying INDEX SKIP SCANS:
1 Optimizer considers the number of unique values of the leading list in the index to meet certain conditions. 3 Optimizer To know the value distribution of the leading list (obtained by analysis / statistics) 4 Suitable 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
Thanks to the masters who participated in the discussion.
