两种常见的索引扫描方式:INDEX FAST FULL SCAN,INDEX FULL SCAN。前者是按照索引的物理结构扫描(就是高水位线以下),后者是按照索引的逻辑结构,即树形结构,叶子节点的双向链表扫描数据。
--制造一些数据
drop table test purge;
create table test as select * from dba_objects where object_id is not null;
alter table test modify object_id not null;
create unique index ind_object_id on test(object_id) nologging;
create index ind_object_name on test(object_name) nologging;
exec dbms_stats.gather_table_stats(user,'test',cascade => true);
--执行测试脚本
alter system flush buffer_cache;
set autotrace traceonlyalter session set events '10046 trace name context forever ,level 12' ;
select object_id from test;
alter session set events '10046 trace name context off' ;
alter system flush buffer_cache;
set autotrace traceonly
alter session set events '10046 trace name context forever ,level 12' ;
select * from test order by object_id;
alter session set events '10046 trace name context off' ;
select object_id
from
test
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4847 0.15 0.14 153 4992 0 72683
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4849 0.15 0.15 153 4992 0 72683
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
Rows Row Source Operation
------- ---------------------------------------------------
72683 INDEX FAST FULL SCAN IND_OBJECT_ID (cr=4992 pr=153 pw=0 time=25509 us cost=43 size=363415 card=72683)(object id 75710)
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4847 0.00 0.00
db file sequential read 1 0.00 0.00
db file scattered read 17 0.00 0.01
SQL*Net message from client 4847 0.00 0.19
********************************************************************************
select *
from
test order by object_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 4847 0.24 0.42 1232 10845 0 72683
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4849 0.24 0.42 1232 10845 0 72683
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 79
Rows Row Source Operation
------- ---------------------------------------------------
72683 TABLE ACCESS BY INDEX ROWID TEST (cr=10845 pr=1232 pw=0 time=251374 us cost=1244 size=7050251 card=72683)
72683 INDEX FULL SCAN IND_OBJECT_ID (cr=4985 pr=168 pw=0 time=25336 us cost=153 size=0 card=72683)(object id 75710)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 4847 0.00 0.00
db file scattered read 154 0.07 0.20
SQL*Net message from client 4847 0.00 1.06
********************************************************************************