Index Full Scan 與 Index Fast Full Scan

index full scan和index fast full scan是指同樣的東西嗎？答案是no。兩者雖然從字面上看起來差不多，但是實現的機制完全不同。我們一起來看看兩者的區別在哪裡? [@more@]

首先來看一下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 Optimizer=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 Optimizer=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 Optimizer=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)

leaf: 0x6c07ec1 113278657 (0: nrow: 262 rrow: 0)

leaf: 0x6c07ebd 113278653 (1: nrow: 518 rrow: 0)

leaf: 0x6c07eb1 113278641 (2: nrow: 524 rrow: 0)

leaf: 0x6c07ead 113278637 (3: nrow: 524 rrow: 0)

leaf: 0x6c07ea9 113278633 (4: nrow: 524 rrow: 0)

leaf: 0x6c07ea5 113278629 (5: nrow: 524 rrow: 0)

leaf: 0x6c07ea1 113278625 (6: nrow: 524 rrow: 0)

leaf: 0x6c07e9d 113278621 (7: nrow: 524 rrow: 0)

leaf: 0x6c07e99 113278617 (8: nrow: 524 rrow: 0)

leaf: 0x6c07e95 113278613 (9: nrow: 532 rrow: 0)

leaf: 0x6c07e91 113278609 (10: nrow: 524 rrow: 0)

leaf: 0x6c07e8d 113278605 (11: nrow: 524 rrow: 0)

leaf: 0x6c07ec8 113278664 (12: nrow: 524 rrow: 0)

leaf: 0x6c07ec4 113278660 (13: nrow: 524 rrow: 0)

leaf: 0x6c07ec0 113278656 (14: nrow: 524 rrow: 0)

leaf: 0x6c07ebc 113278652 (15: nrow: 524 rrow: 0)

leaf: 0x6809bb2 109091762 (16: nrow: 524 rrow: 0)

leaf: 0x6c07eb8 113278648 (17: nrow: 524 rrow: 0)

leaf: 0x6c07eb4 113278644 (18: nrow: 524 rrow: 0)

leaf: 0x6c07eb0 113278640 (19: nrow: 524 rrow: 0)

leaf: 0x6c07eac 113278636 (20: nrow: 524 rrow: 0)

leaf: 0x6809bae 109091758 (21: nrow: 524 rrow: 0)

leaf: 0x6c07ea8 113278632 (22: nrow: 524 rrow: 0)

leaf: 0x6c07ea4 113278628 (23: nrow: 524 rrow: 0)

leaf: 0x6c07ea0 113278624 (24: nrow: 105 rrow: 105)

leaf: 0x6c07e9c 113278620 (25: nrow: 129 rrow: 129)

leaf: 0x6c07eb9 113278649 (26: nrow: 123 rrow: 123)

leaf: 0x6809baa 109091754 (27: nrow: 246 rrow: 246)

leaf: 0x6c07e98 113278616 (28: nrow: 246 rrow: 246)

leaf: 0x6c07e94 113278612 (29: nrow: 246 rrow: 246)

leaf: 0x6809ba6 109091750 (30: nrow: 246 rrow: 246)

leaf: 0x6809bce 109091790 (31: nrow: 246 rrow: 246)

leaf: 0x6809bca 109091786 (32: nrow: 246 rrow: 246)

leaf: 0x6809c05 109091845 (33: nrow: 248 rrow: 248)

leaf: 0x6809c01 109091841 (34: nrow: 246 rrow: 246)

leaf: 0x6809bfd 109091837 (35: nrow: 246 rrow: 246)

leaf: 0x6809bf9 109091833 (36: nrow: 246 rrow: 246)

leaf: 0x6809bf5 109091829 (37: nrow: 246 rrow: 246)

leaf: 0x6809bf1 109091825 (38: nrow: 246 rrow: 246)

leaf: 0x6809bed 109091821 (39: nrow: 246 rrow: 246)

leaf: 0x6809be9 109091817 (40: nrow: 246 rrow: 246)

leaf: 0x6809be5 109091813 (41: nrow: 246 rrow: 246)

leaf: 0x6809be1 109091809 (42: nrow: 246 rrow: 246)

leaf: 0x6809bdd 109091805 (43: nrow: 246 rrow: 246)

leaf: 0x6809bd9 109091801 (44: nrow: 246 rrow: 246)

leaf: 0x6809bd5 109091797 (45: nrow: 246 rrow: 246)

leaf: 0x6809bd1 109091793 (46: nrow: 248 rrow: 248)

leaf: 0x6809bcd 109091789 (47: nrow: 246 rrow: 246)

leaf: 0x6809bc9 109091785 (48: nrow: 246 rrow: 246)

leaf: 0x6809c08 109091848 (49: nrow: 246 rrow: 246)

leaf: 0x6809c04 109091844 (50: nrow: 246 rrow: 246)

leaf: 0x6809c00 109091840 (51: nrow: 246 rrow: 246)

leaf: 0x6809bfc 109091836 (52: nrow: 246 rrow: 246)

leaf: 0x6809bf8 109091832 (53: nrow: 246 rrow: 246)

leaf: 0x6809bf4 109091828 (54: nrow: 246 rrow: 246)

leaf: 0x6809bf0 109091824 (55: nrow: 246 rrow: 246)

leaf: 0x6809bec 109091820 (56: nrow: 246 rrow: 246)

leaf: 0x6809be8 109091816 (57: nrow: 246 rrow: 246)

leaf: 0x6809be4 109091812 (58: nrow: 246 rrow: 246)

leaf: 0x6809be0 109091808 (59: nrow: 248 rrow: 248)

leaf: 0x6809bdc 109091804 (60: nrow: 246 rrow: 246)

leaf: 0x6809bd8 109091800 (61: nrow: 246 rrow: 246)

leaf: 0x6809bd4 109091796 (62: nrow: 246 rrow: 246)

leaf: 0x6809bd0 109091792 (63: nrow: 246 rrow: 246)

leaf: 0x6809bcc 109091788 (64: nrow: 246 rrow: 246)

leaf: 0x6809c07 109091847 (65: nrow: 246 rrow: 246)

leaf: 0x6809c03 109091843 (66: nrow: 246 rrow: 246)

leaf: 0x6809bff 109091839 (67: nrow: 246 rrow: 246)

leaf: 0x6809bfb 109091835 (68: nrow: 246 rrow: 246)

leaf: 0x6809bf7 109091831 (69: nrow: 246 rrow: 246)

leaf: 0x6809bf3 109091827 (70: nrow: 246 rrow: 246)

leaf: 0x6809bef 109091823 (71: nrow: 246 rrow: 246)

leaf: 0x6809beb 109091819 (72: nrow: 248 rrow: 248)

leaf: 0x6809be7 109091815 (73: nrow: 246 rrow: 246)

leaf: 0x6809be3 109091811 (74: nrow: 246 rrow: 246)

leaf: 0x6809bdf 109091807 (75: nrow: 246 rrow: 246)

leaf: 0x6809bdb 109091803 (76: nrow: 246 rrow: 246)

leaf: 0x6809bd7 109091799 (77: nrow: 246 rrow: 246)

leaf: 0x6809bd3 109091795 (78: nrow: 246 rrow: 246)

leaf: 0x6809bcf 109091791 (79: nrow: 246 rrow: 246)

leaf: 0x6809bcb 109091787 (80: nrow: 246 rrow: 246)

leaf: 0x6809c06 109091846 (81: nrow: 246 rrow: 246)

leaf: 0x6809c02 109091842 (82: nrow: 246 rrow: 246)

leaf: 0x6809bfe 109091838 (83: nrow: 246 rrow: 246)

leaf: 0x6809bfa 109091834 (84: nrow: 246 rrow: 246)

leaf: 0x6809ba2 109091746 (85: nrow: 129 rrow: 129)

leaf: 0x6c07eb5 113278645 (86: nrow: 123 rrow: 123)

leaf: 0x6809bf6 109091830 (87: nrow: 246 rrow: 246)

leaf: 0x6809bf2 109091826 (88: nrow: 246 rrow: 246)

leaf: 0x6809bee 109091822 (89: nrow: 246 rrow: 246)

leaf: 0x6809bea 109091818 (90: nrow: 246 rrow: 246)

leaf: 0x6809b9e 109091742 (91: nrow: 246 rrow: 246)

leaf: 0x6809be6 109091814 (92: nrow: 246 rrow: 246)

leaf: 0x6809be2 109091810 (93: nrow: 246 rrow: 246)

leaf: 0x6809bde 109091806 (94: nrow: 246 rrow: 246)

leaf: 0x6809bda 109091802 (95: nrow: 246 rrow: 246)

leaf: 0x6809b9a 109091738 (96: nrow: 246 rrow: 246)

leaf: 0x6809bd6 109091798 (97: nrow: 246 rrow: 246)

leaf: 0x6809bd2 109091794 (98: nrow: 246 rrow: 246)

----- end tree dump

index full scan讀取的是0x6c07ea0 這個塊，而index fast full scan讀取的是 0x6809b9a這個塊也就是包含資料的物理儲存位置最前的塊。分別看一下這兩個塊的內容

0x6c07ea0 =十進位制的113278624 0x6809b9a =十進位制的109091738

SQL> select dbms_utility.data_block_address_file(113278624) "file",dbms_utility.data_block_address_block(113278624) "block" from dual;

file block

---------- ----------

27 32416

SQL> select dbms_utility.data_block_address_file(109091738) "file",dbms_utility.data_block_address_block(109091738)"block" from dual;

file block

---------- ----------

26 39834

SQL> alter system dump datafile 27 block 32416;

SQL> alter system dump datafile 26 block 39834;

block 32416的前10行

row#0[6564] flag: -----, lock: 2

col 0; len 4; (4): c3 02 07 11

col 1; len 6; (6): 07 00 7c 20 00 2b

row#1[6578] flag: -----, lock: 2

col 0; len 4; (4): c3 02 16 4e

col 1; len 6; (6): 07 00 7c 20 00 2a

row#2[6592] flag: -----, lock: 2

col 0; len 4; (4): c3 02 16 4f

col 1; len 6; (6): 07 00 7c 20 00 29

row#3[6606] flag: -----, lock: 2

col 0; len 4; (4): c3 02 16 50

col 1; len 6; (6): 07 00 7c 20 00 28

row#4[6620] flag: -----, lock: 2

col 0; len 4; (4): c3 02 18 02

col 1; len 6; (6): 07 00 7c 20 00 27

row#5[6634] flag: -----, lock: 2

col 0; len 4; (4): c3 02 23 60

col 1; len 6; (6): 07 00 7c 20 00 26

row#6[6648] flag: -----, lock: 2

col 0; len 4; (4): c3 02 24 25

col 1; len 6; (6): 07 00 7c 20 00 25

row#7[6662] flag: -----, lock: 2

col 0; len 4; (4): c3 02 24 28

col 1; len 6; (6): 07 00 7c 20 00 24

row#8[6676] flag: -----, lock: 2

col 0; len 4; (4): c3 02 28 18

col 1; len 6; (6): 07 00 7c 20 00 23

row#9[6690] flag: -----, lock: 2

col 0; len 4; (4): c3 02 42 04

col 1; len 6; (6): 07 00 7c 20 00 22

block 39834的前10行

row#0[4591] flag: -----, lock: 2

col 0; len 4; (4): c3 07 3f 43

col 1; len 6; (6): 02 81 71 f6 00 36

row#1[4605] flag: -----, lock: 2

col 0; len 4; (4): c3 07 3f 44

col 1; len 6; (6): 02 81 71 f6 00 35

row#2[4619] flag: -----, lock: 2

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/670493/viewspace-1026082/，如需轉載，請註明出處，否則將追究法律責任。