index fast full scan 和index full scan的深入探索

对于使用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[@more@]

对于使用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个块。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/111631/viewspace-1029928/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/111631/viewspace-1029928/