Oracle 資料存取路徑測試：從全表掃描到索引快速全掃描

實驗環境建立

本文透過實際測試展示 Oracle 不同的資料存取方式。首先建立測試表並匯入資料：

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：預設情況下的全表掃描

由於 object_id 預設允許 NULL 值，優化器選擇全表掃描：

SET AUTOTRACE TRACE;
SELECT object_id FROM test;

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

測試 2：修改欄位為 NOT NULL 後的索引快速全掃描

將欄位設為 NOT NULL 後，優化器改用索引快速全掃描，成本大幅降低：

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

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

測試 3：強制使用索引全掃描

使用 Hint 強制索引全掃描，按索引順序讀取：

SELECT /*+ INDEX(test ind_TEST_ID) */ object_id FROM test;

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

測試 4：索引範圍掃描

加上 WHERE 條件後使用索引範圍掃描：

SELECT /*+ INDEX(test ind_TEST_ID) */ object_id FROM test 
WHERE object_id < 68926;

| 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 時會根據資料順序選擇不同掃描方式：

• INDEX FAST FULL SCAN：資料按 object_id DESC 排序，返回最大值
• INDEX FULL SCAN：按索引順序讀取，返回最小值

-- 返回 68917~68926（最大值）
SELECT /*+ INDEX(test ind_TEST_ID) */ object_id FROM test WHERE rownum < 11;

-- 返回 10001~10010（最小值）  
SELECT /*+ INDEX(test ind_TEST_ID) */ object_id FROM test WHERE rownum < 11;

結論

欄位的 NULL 屬性直接影響優化器的存取路徑選擇。將查詢欄位設為 NOT NULL 可啟用索引快速全掃描，大幅降低 I/O 成本。實際調優時需結合資料分佈、索引設計與查詢模式綜合考量。