对于使用Oracle的人来说,索引无疑是提高性能必不可少的内容之一,索引的分类也是众所周知的了,但是对于索引的深入研究和不同类型所能达到的不同效果,对于非专业DBA的朋友来说可能会比较零乱,今天兴起,参考了一些文档来对index fast full scan 和index full scan的使用情况做一个实验总结。
确认OS:
[root@localhost ~]# uname -a
Linux localhost.localdomain 2.6.9-5.EL #1 Wed Jan 5 19:22:18 EST 2005 i686 i686 i386 GNU/Linux
确认Oracle version:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 – Production
SQL> insert into kidd_ffs select * from dba_objects where object_id is not null and object_id>10000 and object_id < 20000 order by object_id desc;
9999 rows created.
SQL> commit;
Commit complete.
SQL> analyze table kidd_ffs compute statistics for table for all columns for all indexes;
Table analyzed.
SQL> select object_id from kidd_ffs;
9999 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=9999 Bytes=3
9996)
1 0 TABLE ACCESS (FULL) OF 'KIDD_FFS' (Cost=26 Card=9999 Bytes
=39996)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
919 consistent gets
0 physical reads
0 redo size
142969 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9999 rows processed
我们看到oracle在这个时候会使用TABLE ACCESS (FULL),因为object_id列默认是可以为null的,我们知道B-Tree索引中不存储null值,所以oracle有时候会选择使用全表扫描。
SQL> alter table kidd_ffs modify(object_id not null);
Table altered.
Elapsed: 00:00:00.31
SQL> select object_id from kidd_ffs;
9999 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=9999 Bytes=39
996)
1 0 INDEX (FAST FULL SCAN) OF 'IND_KIDD_ID' (NON-UNIQUE) (Cost
=9 Card=9999 Bytes=39996)
Statistics
----------------------------------------------------------
128 recursive calls
0 db block gets
763 consistent gets
0 physical reads
0 redo size
142969 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
9999 rows processed
从这里我们可以看到,CBO选择了FFS,接下去我们来让oracle选择index full scan
SQL> select /*+ index(kidd_ffs) */ object_id from kidd_ffs;
9999 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=78 Card=9999 Bytes=3
9996)
1 0 INDEX (FULL SCAN) OF 'IND_KIDD_ID' (NON-UNIQUE) (Cost=78 C
ard=9999 Bytes=39996)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
743 consistent gets
0 physical reads
0 redo size
142969 bytes sent via SQL*Net to client
7829 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9999 rows processed
可以看到在被选择列都包含在我们的索引中的时候,IFS和FFS都可以使用,那么他们的区别是什么呢?我觉得必须搞清楚这点,为此我们取两种选择方式的前10行数据来看看不同。
index full scan:
SQL> set autotrace off;
SQL> select /*+ index(kidd_ffs) */ object_id from kidd_ffs where rownum<3000;
OBJECT_ID
----------
12993
12994
12995
12996
12997
12998
12999
2999 rows selected.
Elapsed: 00:00:00.55
index fast full scan:
select object_id from kidd_ffs where rownum<3000;
OBJECT_ID
----------
16931
16932
16933
16934
16935
16936
16937
2999 rows selected.
可以看到index full scan取的数据就是表中最小的3000行,而index fast full scan取的数据则大不相同。这是因为当进行index full scan的时候 oracle定位到索引的root block,然后到branch block(如果有的话),再定位到第一个leaf block, 然后根据leaf block的双向链表顺序读取。它所读取的块都是有顺序的,也是经过排序的。而index fast full scan则不同,它是从段头开始,读取包含位图块,root block,所有的branch block, leaf block,读取的顺序完全有物理存储位置决定,并采取多块读,每次读取db_file_multiblock_read_count个块。
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/111631/viewspace-1029918/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/111631/viewspace-1029918/