索引Index的出現，對Oracle最佳化器而言意味著更多的路徑選擇。隨著CBO的不斷智慧化，一些藉助索引特點出現執行計劃更好的提高SQL的執行效果。

Index Full Scan和Index Fast Full Scan是兩個Oracle索引執行計劃中常常出現的Access Path。兩者存在一些相同點，也有一些差異。

常見的相同點有：

ü 兩者都是針對索引段的執行計劃，而且訪問焦點的是索引葉子節點上的索引值。兩種訪問方式Oracle都不需要進行“回表”操作，操作過程不會涉及到用rowid列表找資料段的過程；

ü 更加智慧化。即使where條件中不包括索引列條件，兩種訪問方式同樣會出現；

兩者的差異在於：

ü Index Full Scan返回的結果集合是有序的，即使在SQL語句中沒有對應的order by字句。而Index Fast Full Scan的結果集合是不保證有序；

ü Index Full Scan不支援並行操作。而Index Fast Full Scan是支援並行操作的。

在實際使用中，我們可以看到兩者的場景差異。資料集合較小的Index操作，往往會選擇Index Full Scan這種比較傳統的動作。而當資料集合較大，索引段較大的時候，Index Fast Full Scan則是CBO的優先選擇。

從實際效果看，兩種訪問方式都是針對所有索引葉子節點的掃描操作，進行諸如計數或者列舉動作。那麼，在執行動作方面，兩個Access Path是有什麼差別呢？本篇來探討一下。

1、環境介紹

本文使用Oracle 11gR2進行試驗，建立實驗資料表和收集統計量。

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

為了進行試驗，我們選擇建立兩個資料表。兩個結構相同但是資料量存在差異。首先是小資料表。

--小資料表

SQL> create table t_small as select * from dba_objects where rownum<10;

Table created

--非空列設定

SQL> alter table t_small modify object_id not null;

Table altered

--索引結構

SQL> create index idx_t_small_id on t_small(object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T_SMALL',cascade => true);

PL/SQL procedure successfully completed

資料表t_small只有包括9條資料。段分配資訊和資料塊資訊如下：

SQL> select segment_name, blocks, extents from dba_segments where wner='SYS' and segment_name in ('T_SMALL','IDX_T_SMALL_ID');

SEGMENT_NAME BLOCKS EXTENTS

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

T_SMALL 8 1

IDX_T_SMALL_ID 8 1

SQL> select num_rows, blocks from dba_tables where wner='SYS' and table_name='T_SMALL';

NUM_ROWS BLOCKS

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

9 1

SQL> select BLEVEL, LEAF_BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from dba_indexes where wner='SYS' and index_name = 'IDX_T_SMALL_ID';

BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS

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

0 1 1 1 9

資料表t_small高水位線（HWM）下只有一個資料塊。對應索引idx_t_small_id包括的葉子塊也只有一個。T_small是一個很小的資料表。

下面建立大表。

SQL> create table t_big as select * from dba_objects;

Table created

SQL> alter table t_big modify object_id not null;

Table altered

SQL> create index idx_t_big_id on t_big(object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T_BIG',cascade => true);

PL/SQL procedure successfully completed

對應的儲存資訊為：

SQL> select segment_name, blocks, extents from dba_segments where wner='SYS' and segment_name in ('T_BIG','IDX_T_BIG_ID');

SEGMENT_NAME BLOCKS EXTENTS

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

T_BIG 1152 24

IDX_T_BIG_ID 256 17

SQL> select num_rows, blocks from dba_tables where wner='SYS' and table_name='T_BIG';

NUM_ROWS BLOCKS

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

72689 1034

SQL> select BLEVEL, LEAF_BLOCKS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, NUM_ROWS from dba_indexes where wner='SYS' and index_name = 'IDX_T_BIG_ID';

BLEVEL LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY NUM_ROWS

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

1 161 1 1 72689

注意：t_big資料表高水位線下1034個資料塊，分佈在24個extent上。索引idx_t_big對應17個分割槽，葉子塊有161個（注意這個數字）。

2、常規執行計劃分析

我們首先看一下CBO對於大小兩個資料表在相同SQL結構下的不同選擇。

SQL> explain plan for select count(*) from t_small;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 1767817138

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

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

| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| IDX_T_SMALL_ID | 9 | 1 (0)| 00:00:01 |

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

9 rows selected

我們的SQL中沒有where條件，也沒有明確的指定索引路徑hint，CBO依然選擇了索引路徑。因為，我們明確指定了object_id列為非空，並且在其上構建了索引。

對CBO而言，有至少兩條備選路徑可以選擇。其一是傳統的FTS（Full Table Scan），從資料表段頭開始掃描所有資料塊，直到HWM。另一條是藉助索引的“蹊徑”。

在得到object_id列非空的前提下，CBO認為索引樹idx_t_small_id葉子節點的數目實際上就是資料錶行數。對索引段讀取的I/O量明顯要小於資料表段，而且不需要“回表”操作。於是，CBO選擇單獨讀取索引結構進行計數，也就是Index Full Scan。

那麼，對資料量增加的t_big，事情是如何呢？

SQL> explain plan for select count(*) from t_big;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2892922722

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

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

| 0 | SELECT STATEMENT | | 1 | 43 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FAST FULL SCAN| IDX_T_BIG_ID | 72689 | 43 (0)| 00:00:01 |

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

9 rows selected

相同的環境，不同的資料量，CBO做出了不同的路徑選擇。在t_big的計數操作中，Oracle選擇了Index Fast Full Scan路徑。但是，Index Fast Full Scan的效果和Index Full Scan的結果是一樣，都是對索引段節點計數。

從實際情況來看，CBO在兩個Access Path的選擇過程中遵守了這樣的原則：如果資料表很小，Index Full Scan方式更加容易被選擇到。反之，Index Fast Full Scan更加容易出現。

當然，我們可以透過hint指定的方法，強令Oracle選擇路徑。

SQL> explain plan for select /*+index(t_big)*/count(*) from t_big;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2587926039

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

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

| 0 | SELECT STATEMENT | | 1 | 162 (0)| 00:00:03 |

| 1 | SORT AGGREGATE | | 1 | | |

| 2 | INDEX FULL SCAN| IDX_T_BIG_ID | 72689 | 162 (0)| 00:00:03 |

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

9 rows selected

透過hint控制，我們也可以讓t_big的操作走index full scan，但是我們要注意到，成本值為162，遠遠高於index fast full scan的43。

那麼，index fast full scan和index full scan在行為上的差異是什麼呢？兩種操作必然有獨特的特點，讓CBO在進行評估時候有不同的取捨。下面，我們將使用10046對兩種操作進行更加細緻的分析。

Index Full Scan和Index Fast Full Scan行為差異分析（上）

相關文章