oracle跳躍式索引掃描測試

標籤：oracle跳躍式索引掃描測試

Oracle 中我們知道能夠使用跳躍式索引掃描(Index Skip Scan).然而,能利用跳躍式索引掃描的情況其實是有些限制的
CREATE TABLE test AS SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects;

SQL> CREATE TABLE test AS SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects;

Table created.

Elapsed: 00:00:02.78
SQL> desc test;
 Name                               Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 A                                NUMBER
 B                                NUMBER
 C                                NUMBER
 D                                NUMBER
 E                                NUMBER

SELECT DISTINCT COUNT (a) FROM test;
SQL> SELECT DISTINCT COUNT (a) FROM test;

  COUNT(A)
----------
     84394



CREATE INDEX test_idx ON test(a,b,c);

SQL> create index test_idx on test(a,b,c);

Index created.


ANALYZE TABLE test COMPUTE STATISTICS;
SET autotrace traceonly explain;
SELECT *  FROM test WHERE b = 99;

SQL> analyze table test compute statistics;

Table analyzed.

Elapsed: 00:00:02.46
SQL> set autotrace traceonly explain;
SQL> select * from test where b=99;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    20 |    96   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    20 |    96   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"=99)
   
 -可見這裡CBO選擇了全表掃描
我們接著做另一個測試
 drop table test;
 CREATE TABLE test  AS SELECT DECODE(MOD(ROWNUM,2), 0, ‘1‘, ‘2‘ ) a,ROWNUM-1 b,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e FROM all_objects
 set autotrace off
 select distinct a from test;
 SQL> select distinct a from test;

A
-
1
2

Elapsed: 00:00:00.08

 CREATE INDEX test_idx ON test(a,b,c)
 
 SQL> SELECT *  FROM test WHERE b = 99;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2705879578

----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |     1 |    17 |     4     (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    17 |     4     (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN        | TEST_IDX |     1 |       |     3     (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=99)
       filter("B"=99)

結論：
Oracle的最佳化器(這裡指的是CBO)能對查詢應用Index Skip Scans至少要有幾個條件:

1 最佳化器認為是合適的.
2 索引中的前置列的唯一值的數量能滿足一定的條件.
3 最佳化器要知道前置列的值分布(通過分析/統計表得到)
4 合適的SQL語句
       

本文出自 “梁小明的部落格” 部落格，請務必保留此出處http://7038006.blog.51cto.com/7028006/1835453

oracle跳躍式索引掃描測試