Oracle学习笔记索引执行计划中的关键字（十三）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle学习笔记索引执行计划中的关键字（十三），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10050字，纯文字阅读大概需要15分钟。
内容图文
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
内容总结
以上是互联网集市为您收集整理的Oracle学习笔记索引执行计划中的关键字（十三）全部内容，希望文章能够帮你解决Oracle学习笔记索引执行计划中的关键字（十三）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】