Index Full Scan vs Index Fast Full Scan
index full scan和index fast full scan是指同样的东西吗?答案是no。两者虽然从字面上看起来差不多,但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句:
SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
SQL> CREATE INDEX ind_test_id ON TEST(object_id);
SQL> INSERT INTO TEST
SELECT *
FROM dba_objects
WHERE object_id IS NOT NULL AND object_id > 10000
ORDER BY object_id DESC;
17837 rows created.
SQL> analyze table test compute statistics for table for all columns for all indexes;
Table analyzed.
SQL> set autotrace trace;
SQL> select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
1 0 TABLE Access (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
这时候 Oracle会选择全表扫描,因为 object_id 列默认是可以为null的,来修改成 not null:
SQL>alter table test modify(object_id not null);
SQL> select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
当然我们也可以使用index full scan:
SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
1 0 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
我们看到了两者都可以在这种情况下使用,那么他们有什么区别呢?有个地方可以看出两者的区别, 来看一下两者的输出结果,为了让大家看清楚一点,我们只取10行。
INDEX FAST FULL SCAN
SQL> select object_id from test where rownum<11;
OBJECT_ID
----------
66266
66267
66268
66269
66270
66271
66272
66273
66274
66275
10 rows selected.
INDEX FULL SCAN
SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11;
OBJECT_ID
----------
10616
12177
12178
12179
12301
13495
13536
13539
13923
16503
10 rows selected.
可以看到两者的结果完全不一样,这是为什么呢?这是因为当进行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个块。
这就是为什么两者的结果区别如此之大的原因,我们再仔细跟踪一下这两条语句。首先来看一下索引的结构
SQL> select object_id from dba_objects where object_name='IND_TEST_ID';
OBJECT_ID
----------
70591
索引的object_id为70591,使用tree dump可以看到索引树的结构 SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 70591';
----- begin tree dump
branch: 0x6809b8d 109091725 (0: nrow: 100, level: 1)
leaf: 0x6809b96 109091734 (-1: nrow: 294 rrow: 0)
........
index full scan和index fast full scan是指同样的东西吗?答案是no。两者虽然从字面上看起来差不多,但是实现的机制完全不同。我们一起来看看两者的区别在哪里?
首先来看一下IFS,FFS能用在哪里:在一句sql中,如果我们想搜索的列都包含在索引里面的话,那么index full scan 和 index fast full scan 都可以被采用代替full table scan。比如以下语句:
SQL> CREATE TABLE TEST AS SELECT * FROM dba_objects WHERE 0=1;
SQL> CREATE INDEX ind_test_id ON TEST(object_id);
SQL> INSERT INTO TEST
SELECT *
FROM dba_objects
WHERE object_id IS NOT NULL AND object_id > 10000
ORDER BY object_id DESC;
17837 rows created.
SQL> analyze table test compute statistics for table for all columns for all indexes;
Table analyzed.
SQL> set autotrace trace;
SQL> select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=68 Card=17837 Bytes=71348)
1 0 TABLE Access (FULL) OF 'TEST' (Cost=68 Card=17837 Bytes=71348)
这时候 Oracle会选择全表扫描,因为 object_id 列默认是可以为null的,来修改成 not null:
SQL>alter table test modify(object_id not null);
SQL> select object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=11 Card=17837 Bytes=71348)
1 0 INDEX (FAST FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=11 Card=17837 Bytes=71348)
当然我们也可以使用index full scan:
SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test;
17837 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=41 Card=17837 Bytes=71348)
1 0 INDEX (FULL SCAN) OF 'IND_TEST_ID' (NON-UNIQUE) (Cost=101 Card=17837 Bytes=71348)
我们看到了两者都可以在这种情况下使用,那么他们有什么区别呢?有个地方可以看出两者的区别, 来看一下两者的输出结果,为了让大家看清楚一点,我们只取10行。
INDEX FAST FULL SCAN
SQL> select object_id from test where rownum<11;
OBJECT_ID
----------
66266
66267
66268
66269
66270
66271
66272
66273
66274
66275
10 rows selected.
INDEX FULL SCAN
SQL> select/*+ index(test ind_TEST_ID)*/ object_id from test where rownum<11;
OBJECT_ID
----------
10616
12177
12178
12179
12301
13495
13536
13539
13923
16503
10 rows selected.
可以看到两者的结果完全不一样,这是为什么呢?这是因为当进行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个块。
这就是为什么两者的结果区别如此之大的原因,我们再仔细跟踪一下这两条语句。首先来看一下索引的结构
SQL> select object_id from dba_objects where object_name='IND_TEST_ID';
OBJECT_ID
----------
70591
索引的object_id为70591,使用tree dump可以看到索引树的结构 SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 70591';
----- begin tree dump
branch: 0x6809b8d 109091725 (0: nrow: 100, level: 1)
leaf: 0x6809b96 109091734 (-1: nrow: 294 rrow: 0)
........
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14876437/viewspace-555622/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14876437/viewspace-555622/