Oracle INDEX FAST FULL SCAN与 INDEX FULL SCAN

淡淡的烟草味﹌ 2022-08-21 13:56 206阅读 0赞

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