准备:
create index ind_id_ot on id(object_type,namespace) tablespace tsind01
index range scan是一种很常见的表访问方式
在INDEX Range SCAN中,Oracle访问毗邻的索引条目,然后根据索引里面的rowid去检索表的记录SQL> set autotrace traceonly
SQL> set linesize 200
SQL> select * from id where object_type='TABLE';
2881 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2881 | 208K| 125 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 2881 | 208K| 125 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_ID_OT | 2881 | | 8 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
584 consistent gets
0 physical reads
0 redo size
320300 bytes sent via SQL*Net to client
2632 bytes received via SQL*Net from client
194 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2881 rows processed
SQL>
SQL> select * from id where object_type='TABLE' and namespace=2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 1850 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 25 | 1850 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID_OT | 25 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "NAMESPACE"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
index skip scan 利用了前导列没有在where 条件的索引。即充分利用了现有索引又适当加快了数据查询速度。
ORACLE官方说,在前导列唯一值较少的情况下,才会用到index skip can。这个其实好理解,就是入口要少,这也是skip scan的条件
1516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3621836617
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1516 | 109K| 492 (0)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 1516 | 109K| 492 (0)| 00:00:06 |
|* 2 | INDEX SKIP SCAN | IND_ID_OT | 1516 | | 456 (0)| 00:00:06 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAMESPACE"=2)
filter("NAMESPACE"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
573 consistent gets
0 physical reads
0 redo size
168541 bytes sent via SQL*Net to client
1631 bytes received via SQL*Net from client
103 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1516 rows processed
SQL> select * from id where object_type='TABLE' and namespace=1;
2881 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2823458674
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2720 | 196K| 75 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ID | 2720 | 196K| 75 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID_OT | 2720 | | 10 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "NAMESPACE"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
587 consistent gets
0 physical reads
0 redo size
320300 bytes sent via SQL*Net to client
2632 bytes received via SQL*Net from client
194 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2881 rows processed
为了验证index skip scan 的使用条件,我们重新建立索引如下
create index ind_id_ot on id(object_id,namespace) tablespace tsind01
SQL> select * from id where namespace=2;
1516 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 377127794
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1516 | 109K| 522 (1)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| ID | 1516 | 109K| 522 (1)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAMESPACE"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1944 consistent gets
0 physical reads
0 redo size
75174 bytes sent via SQL*Net to client
1631 bytes received via SQL*Net from client
103 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1516 rows processed
如上object_id为唯一值列,会有很多值。虽然有相应的索引包含了namespace 列也没有使用到该索引