Oracle 數據訪問路徑深度解析：從全表掃描到索引快速全掃描

實驗環境建立

為了直觀展示 Oracle 優化器在不同條件下選擇的數據訪問方式，我們首先構建一個測試表 TEST，並建立索引、收集統計信息：

CREATE TABLE test AS SELECT * FROM dba_objects WHERE 0=1;
CREATE INDEX ind_test_id ON test(object_id);
INSERT INTO test SELECT * FROM dba_objects WHERE object_id IS NOT NULL AND object_id > 10000 ORDER BY object_id DESC;
ANALYZE TABLE test COMPUTE STATISTICS FOR TABLE FOR ALL COLUMNS FOR ALL INDEXES;

訪問路徑演示與分析

1. 全表掃描

當查詢無 WHERE 條件，且欄位允許 NULL 時，優化器傾向於全表掃描。

SQL> SET AUTOTRACE TRACE
SQL> SELECT object_id FROM test;

Execution Plan
----------------------------------------------------------
| Id | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|  1 | TABLE ACCESS FULL | TEST | 58650 |   229K|   239   (1)| 00:00:03 |

2. 索引快速全掃描

將 object_id 修改為 NOT NULL 後，同樣的查詢改用索引快速全掃描，成本大幅降低。

SQL> ALTER TABLE test MODIFY (object_id NOT NULL);
SQL> SELECT object_id FROM test;

Execution Plan
----------------------------------------------------------
| Id | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|  1 | INDEX FAST FULL SCAN  | IND_TEST_ID| 58650 |   229K|    66   (0)| 00:00:01 |

原理：索引快速全掃描以多區塊讀取方式掃描索引葉子塊，不遵循索引順序，適用於索引欄位為 NOT NULL 且需讀取大部分數據的場景。

3. 索引全掃描

使用 Hint 強制走索引全掃描，會按照索引順序單塊讀取，成本反而高於全表掃描。

SQL> SELECT /*+ INDEX(test ind_test_id) */ object_id FROM test;

Execution Plan
----------------------------------------------------------
| Id | Operation        | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|  1 | INDEX FULL SCAN  | IND_TEST_ID| 58650 |   229K|   240   (1)| 00:00:03 |

4. 索引範圍掃描

加上範圍條件 WHERE object_id < 68926 時，優化器選擇索引範圍掃描。

SQL> SELECT /*+ INDEX(test ind_test_id) */ object_id FROM test WHERE object_id < 68926;

Execution Plan
----------------------------------------------------------
| Id | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
|  1 | INDEX RANGE SCAN  | IND_TEST_ID| 57903 |   226K|   237   (1)| 00:00:03 |

5. ROWNUM 與訪問路徑的微妙關係

當使用 ROWNUM < 11 限制輸出行數時，訪問路徑會因 Hint 與條件順序而改變：

• 無 Hint 時：優化器判斷只需少量行，選擇 INDEX FAST FULL SCAN，返回的是物理順序靠後的數據（因表建立時按 DESC 排序）。
• 有 Hint 時：強制索引全掃描，返回索引順序的前 10 行（即最小的 10 個 ID）。

-- 無 Hint，INDEX FAST FULL SCAN
SELECT object_id FROM test WHERE rownum < 11;
-- 結果: 68917, 68918... (較大值)

-- 有 Hint，INDEX FULL SCAN
SELECT /*+ INDEX(test ind_test_id) */ object_id FROM test WHERE rownum < 11;
-- 結果: 10001, 10002... (較小值)

核心結論

1. NULL 屬性影響巨大：欄位若允許 NULL，索引不包含全表數據，優化器無法使用索引快速全掃描覆蓋全表查詢。
2. 訪問路徑決定數據順序：Index Fast Full Scan 返回物理存儲順序；Index Full Scan 返回邏輯索引順序。配合 ROWNUM 時極易產生業務邏輯誤解。
3. 成本不等於時間：Index Full Scan 成本 (240) 高於 Full Table Scan (239)，實際執行時間可能相近，但 I/O 模式不同（單塊 vs 多塊讀取）。