索引跳跃扫描

最新推荐文章于 2025-03-23 13:23:04 发布

人生没有后悔

最新推荐文章于 2025-03-23 13:23:04 发布

阅读量2k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle sql优化

本文链接：https://blog.youkuaiyun.com/yftianxia/article/details/16104959

oracle sql优化专栏收录该内容

4 篇文章

订阅专栏

从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多

SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)
----------
72503

SQL> select object_type,count(*) from t group by object_type;

OBJECT_TYPE COUNT(*)

-------------------------------------- ----------
EDITION 1
INDEX PARTITION 320
CONSUMER GROUP 25
SEQUENCE 240
TABLE PARTITION 163
SCHEDULE 3
QUEUE 17
RULE 1
JAVA DATA 309
PROCEDURE 159
OPERATOR 55

OBJECT_TYPE COUNT(*)
-------------------------------------- ----------
LOB PARTITION 1
DESTINATION 2
WINDOW 9
SCHEDULER GROUP 4
LOB 153
PACKAGE 1327
PACKAGE BODY 1267
LIBRARY 183
PROGRAM 19
RULE SET 23
CONTEXT 7

OBJECT_TYPE COUNT(*)
-------------------------------------- ----------
TYPE BODY 240
JAVA RESOURCE 834
XML SCHEMA 51
TRIGGER 617
JOB CLASS 13
UNDEFINED 9
DIRECTORY 14
MATERIALIZED VIEW 3
TABLE 3060
INDEX 4036
SYNONYM 28061

OBJECT_TYPE COUNT(*)
-------------------------------------- ----------
VIEW 5170
FUNCTION 302
JAVA CLASS 22920
JAVA SOURCE 2
INDEXTYPE 9
CLUSTER 10
TYPE 2826
RESOURCE PLAN 10
JOB 14
EVALUATION CONTEXT 14

SQL> create index indx_t on t(object_type,object_name);

Index created.

SQL> ANALYZE TABLE T COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /

Table analyzed.

where子句中没有索引前导列的情况

SQL> select * from t where object_name= 'T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2722864248

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

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

| 0 | SELECT STATEMENT | | 2 | 200 | 47 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 200 | 47 (0)| 00:00:01 |

|* 2 | INDEX SKIP SCAN | INDX_T | 2 | | 45 (0)| 00:00:01 |

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

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

2 - access("OBJECT_NAME"='T')
filter("OBJECT_NAME"='T')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
39 consistent gets
0 physical reads
0 redo size
1392 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

强制不使用索引

SQL> select /*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'T';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 200 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 2 | 200 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------

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

1 - filter("OBJECT_NAME"='T')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1038 consistent gets
0 physical reads
0 redo size
1389 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

consistent gets（1038-39=999）比使用跳跃扫描大很多，

所以索引跳跃式扫描比全索引扫描要快的多

优化器根据索引中的前导列(索引到的第一列)的唯一值的数量决定是否使用Skip Scan

CREATE TABLE t1 AS
SELECT ROWNUM a,ROWNUM-1 b ,ROWNUM-2 c,ROWNUM-3 d,ROWNUM-4 e
FROM all_objects

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

Table created.

SQL> select count(a) from t1;

COUNT(A)
----------
72504

SQL> select distinct count(a) from t1;

COUNT(A)
----------
72504

SQL> CREATE INDEX test_idx ON test(a,b,c)

SQL> create index t_index on t1(a,b);

Index created.

SQL> ANALYZE TABLE T1 COMPUTE STATISTICS
2 FOR TABLE
3 FOR ALL INDEXES
4 FOR ALL INDEXED COLUMNS
5 /

Table analyzed.

SQL> set autotrace traceonly;

SQL> select * from t1 where b=99; 没有使用跳跃扫描

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 82 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 27 | 82 (2)| 00:00:01 |
--------------------------------------------------------------------------

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

1 - filter("B"=99)

Statistics
----------------------------------------------------------
64 recursive calls
0 db block gets
305 consistent gets
0 physical reads
0 redo size
647 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>