INDEX RANGE SCAN：索引范围查询
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘T‘,estimate_percent => 10,method_opt=> ‘for all indexed columns‘,cascade=>TRUE) ;
select * from t where object_id=8;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1394 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX UNIQUE SCAN：索引唯一性查询
--请注意这个INDEX UNIQUE SCAN扫描方式,在唯一索引情况下使用。
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create unique index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------|
统计信息
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1298 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
TABLE ACCESS BY USER ROWID：索引rowid查询
--请注意这个TABLE ACCESS BY USER ROWID扫描方式,直接根据rowid来访问，最快的访问方式！
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
--注意，这里连索引都没建!
--create index idx_object_id on t(object_id);
set autotrace off
select rowid from t where object_id=8;
ROWID
------------------
AAAZxiAAGAAAB07AAH
set autotrace traceonly
set linesize 1000
select * from t where object_id=8 and rowid=‘AAAZxiAAGAAAB07AAH‘;
执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY USER ROWID| T | 1 | 219 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
1391 bytes sent via SQL*Net to client
415 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：索引全扫描
---请记住这个INDEX FULL SCAN扫描方式，并体会与INDEX FAST FULL SCAN的区别
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select * from t order by object_id;
执行计划
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88780 | 17M| 1208 (1)| 00:00:15 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 88780 | 17M| 1208 (1)| 00:00:15 |
| 2 | INDEX FULL SCAN | IDX_OBJECT_ID | 88780 | | 164 (1)| 00:00:02 |
---------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
10873 consistent gets
0 physical reads
0 redo size
8116181 bytes sent via SQL*Net to client
54040 bytes received via SQL*Net from client
4877 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
73130 rows processed
INDEX FAST FULL SCAN：索引min和max全扫描
--请注意这个INDEX FULL SCAN (MIN/MAX)扫描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
commit;
create index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select max(object_id) from t;
执行计划
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
431 bytes sent via SQL*Net to client
415 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 (MINMAX)：索引快速全扫描
---请记住这个INDEX FAST FULL SCAN扫描方式，并体会与INDEX FULL SCAN的区别
drop table t purge;
create table t as select * from dba_objects ;
update t set object_id=rownum;
commit;
alter table T modify object_id not null;
create index idx_object_id on t(object_id);
set autotrace traceonly
set linesize 1000
select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 88780 | 49 (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
INDEX SKIP SCAN：跳跃索引
--请记住这个INDEX SKIP SCAN扫描方式
drop table t purge;
create table t as select * from dba_objects;
update t set object_type=‘TABLE‘ ;
commit;
update t set object_type=‘VIEW‘ where rownum<=30000;
commit;
create index idx_type_id on t(object_type,object_id);
exec dbms_stats.gather_table_stats(ownname => ‘LJB‘,tabname => ‘T‘,estimate_percent => 10,method_opt=> ‘for all indexed columns‘,cascade=>TRUE) ;
set autotrace traceonly
set linesize 1000
select * from t where object_id=8;
执行计划
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 94 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 94 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TYPE_ID | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=8)
filter("OBJECT_ID"=8)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1401 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
TABLE ACCESS BY INDEX ROWID：回表
drop table t purge; create table t as select * from dba_objects; update t set object_id=rownum; commit; create index idx_object_id on t(object_id); set autotrace traceonly explain set linesize 1000 select object_id from t where object_id=2 and object_type=‘TABLE‘; --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 216 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T | 9 | 216 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 12 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- TABLE ACCESS BY INDEX ROWID消失了。 create index idx_id_type on t(object_id,object_type); select object_id from t where object_id=2 and object_type=‘TABLE‘; 执行计划 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9 | 216 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_ID_TYPE | 9 | 216 | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------
原文：https://www.cnblogs.com/sunliyuan/p/12330828.html