Oracle INDEX FAST FULL SCAN与 INDEX FULL SCAN

来源：互联网发布：java遍历json对象编辑：程序博客网时间：2024/05/18 01:38

两种常见的索引扫描方式：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 traceonly
alter 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)

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 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
********************************************************************************

0 0