INDEX SKIP SCAN和INDEX FULL SCAN
时间:2016-05-29 02:41 来源:linux.it.net.cn 作者:IT
INDEX SKIP SCAN:跳跃式索引能够通过使用非前缀列提高索引的查询性能.通常情况下,扫描索引块比扫描表数据块快.
跳跃式索引能够使复合索引逻辑上拆分分一个个小的子索引.在跳跃式扫描中,复合索引的首列不会被限定在查询中,因为它是跳跃式的.
数据库确定了一定数量的逻辑子索引通过在首列的去重的值中.当数据库中复合索引的前导列有很少的去重的值和索引中有非前导列有很多重复的值时跳跃式扫描就会有很多优点.
当复合索引的前导列没有被查询谓词界定时,数据库会选择跳跃式索引.
INDEX FULL SCAN:索引全扫描会排除排序操作,因为数据已经被按索引键值排序.读数据块是逐个读的.数据库在如下任何一个情况时可能会使用full scan:
An ORDER BY clause that meets the following requirements is present in the query(有order by子句存在于查询中):
All of the columns in the ORDER BY clause must be in the index.(在order by中的所有列必须被索引)
The order of the columns in the ORDER BY clause must match the order of the leading index columns.(在order by子句中排序的列必须匹配排序的前导列)
The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.(order by子句中包含在索引中的所有列或者索引列的一个子集)
举例:
SESSION1:
SQL> create table emp5 as select * from emp;
Table created.
SQL> create index skip1 on emp5(job,empno);
Index created.
SQL> commit;
Commit complete.
SQL> set autotrace traceon exp stat;
SQL> select count(*) from emp5 where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 853491148
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FULL SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Note
-----
- dynamic sampling used for this statement (level=2) -------此处为动态信息采集,因为表没有被分析.
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----------可以看出新建表在没有被分析的情况下,默认是走INDEX FULL SCAN.
----------此时再开一个session2,执行如下的查询:
SESSION2:
SQL> set autotrace traceon exp stat;
SQL> set serveroutput on
SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900;-----此处加hint强制走index skip scan,和上面的index full scan形成对比
Execution Plan
----------------------------------------------------------
Plan hash value: 3156092949
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Note
-----
- dynamic sampling used for this statement (level=2) -----因为表没有被分析,索引此处也是动态信息采集
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-------此时看不出具体的区别,因为表很小,并且已经被缓存到SGA中.在session2中再执行以下操作:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3156092949
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
62 recursive calls
0 db block gets
56 consistent gets
15 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
-------在SESSION1也执行以下操作,确保不受缓存的影响:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select count(*) from emp5 where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 853491148
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX FULL SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
62 recursive calls
0 db block gets
56 consistent gets
17 physical reads ----------------此时可以看出微小的区别,即跳跃式索引产生的物理读比索引全扫描产生的物理读小.
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
----------在SESSION1中分析这个表,再次看分析后的执行计划:
SQL> analyze table emp5 compute statistics;
Table analyzed.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from emp5 where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3156092949
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Statistics
----------------------------------------------------------
27 recursive calls
0 db block gets
50 consistent gets
15 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
-------------可见分析过后,对于复合索引SKIP1,前导列没有在where的条件下的情况,数据库会自动选择INDEX SKIP SCAN.
-------------在SESSION2中,执行以下操作,看具体区别:
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900;
Execution Plan
----------------------------------------------------------
Plan hash value: 3156092949
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 3 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7900)
filter("EMPNO"=7900)
Statistics
----------------------------------------------------------
27 recursive calls
0 db block gets
50 consistent gets
11 physical reads -------------------此处少了一些,应该是缓存的游标或者在LRU的热端,导致少了一些物理读
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
总结:最好把表分析过后,刷新缓存,这样做实验才有意义.如果走动态采样,可以执行hint看区别.
索引跳跃式扫描可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多
(责任编辑：IT)
| INDEX SKIP SCAN:跳跃式索引能够通过使用非前缀列提高索引的查询性能.通常情况下,扫描索引块比扫描表数据块快. 跳跃式索引能够使复合索引逻辑上拆分分一个个小的子索引.在跳跃式扫描中,复合索引的首列不会被限定在查询中,因为它是跳跃式的. 数据库确定了一定数量的逻辑子索引通过在首列的去重的值中.当数据库中复合索引的前导列有很少的去重的值和索引中有非前导列有很多重复的值时跳跃式扫描就会有很多优点. 当复合索引的前导列没有被查询谓词界定时,数据库会选择跳跃式索引. INDEX FULL SCAN:索引全扫描会排除排序操作,因为数据已经被按索引键值排序.读数据块是逐个读的.数据库在如下任何一个情况时可能会使用full scan: An ORDER BY clause that meets the following requirements is present in the query(有order by子句存在于查询中): All of the columns in the ORDER BY clause must be in the index.(在order by中的所有列必须被索引) The order of the columns in the ORDER BY clause must match the order of the leading index columns.(在order by子句中排序的列必须匹配排序的前导列) The ORDER BY clause can contain all of the columns in the index or a subset of the columns in the index.(order by子句中包含在索引中的所有列或者索引列的一个子集) 举例: SESSION1: SQL> create table emp5 as select * from emp; Table created. SQL> create index skip1 on emp5(job,empno); Index created. SQL> commit; Commit complete. SQL> set autotrace traceon exp stat; SQL> select count(*) from emp5 where empno=7900; Execution Plan ---------------------------------------------------------- Plan hash value: 853491148 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FULL SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Note ----- - dynamic sampling used for this statement (level=2) -------此处为动态信息采集,因为表没有被分析. Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed ----------可以看出新建表在没有被分析的情况下,默认是走INDEX FULL SCAN. ----------此时再开一个session2,执行如下的查询: SESSION2: SQL> set autotrace traceon exp stat; SQL> set serveroutput on SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900;-----此处加hint强制走index skip scan,和上面的index full scan形成对比 Execution Plan ---------------------------------------------------------- Plan hash value: 3156092949 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Note ----- - dynamic sampling used for this statement (level=2) -----因为表没有被分析,索引此处也是动态信息采集 Statistics ---------------------------------------------------------- 4 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed -------此时看不出具体的区别,因为表很小,并且已经被缓存到SGA中.在session2中再执行以下操作: SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900; Execution Plan ---------------------------------------------------------- Plan hash value: 3156092949 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 62 recursive calls 0 db block gets 56 consistent gets 15 physical reads 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed -------在SESSION1也执行以下操作,确保不受缓存的影响: SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> select count(*) from emp5 where empno=7900; Execution Plan ---------------------------------------------------------- Plan hash value: 853491148 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX FULL SCAN| SKIP1 | 1 | 13 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 62 recursive calls 0 db block gets 56 consistent gets 17 physical reads ----------------此时可以看出微小的区别,即跳跃式索引产生的物理读比索引全扫描产生的物理读小. 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed ----------在SESSION1中分析这个表,再次看分析后的执行计划: SQL> analyze table emp5 compute statistics; Table analyzed. SQL> alter system flush shared_pool; System altered. SQL> alter system flush buffer_cache; System altered. SQL> select count(*) from emp5 where empno=7900; Execution Plan ---------------------------------------------------------- Plan hash value: 3156092949 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 50 consistent gets 15 physical reads 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed -------------可见分析过后,对于复合索引SKIP1,前导列没有在where的条件下的情况,数据库会自动选择INDEX SKIP SCAN. -------------在SESSION2中,执行以下操作,看具体区别: SQL> alter system flush buffer_cache; System altered. SQL> alter system flush shared_pool; System altered. SQL> select /*+ index_ss(emp5 skip1) */ count(*) from emp5 where empno=7900; Execution Plan ---------------------------------------------------------- Plan hash value: 3156092949 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | |* 2 | INDEX SKIP SCAN| SKIP1 | 1 | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPNO"=7900) filter("EMPNO"=7900) Statistics ---------------------------------------------------------- 27 recursive calls 0 db block gets 50 consistent gets 11 physical reads -------------------此处少了一些,应该是缓存的游标或者在LRU的热端,导致少了一些物理读 0 redo size 526 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed 总结:最好把表分析过后,刷新缓存,这样做实验才有意义.如果走动态采样,可以执行hint看区别. 索引跳跃式扫描可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多 (责任编辑：IT) |