創建索引對 SQL 執行計劃影響的實證驗證：運行中 SQL 不受新索引干擾

核心結論

創建索引不會改變已在運行的 SQL 執行計劃，也就不會影響正在執行的 SQL 語句。 Oracle 在 SQL 開始執行前確定執行計劃，執行過程中新建的索引對該游標無效。

驗證實驗設計

-- 1. 建表並插入測試數據
CREATE TABLE test (id NUMBER, fid NUMBER, name VARCHAR2(30), bid_columns CHAR(2000));
INSERT INTO test VALUES (1, 0, 'OBJECT', '0');
INSERT INTO test VALUES (2, 1, 'TABLE', '0');
-- 批量插入模擬數據量
INSERT INTO test SELECT 20000+ROWNUM, 2, TABLE_NAME, '0' FROM DBA_TABLES;
INSERT INTO test SELECT 30000+ROWNUM, 3, INDEX_NAME, '0' FROM DBA_INDEXES;
INSERT INTO test SELECT 40000+ROWNUM, 4, VIEW_NAME, '0' FROM DBA_VIEWS;
INSERT INTO test SELECT 50000+ROWNUM, 5, TABLE_NAME, '0' FROM DBA_TABLES;
COMMIT;

-- 2. 會話 A：啟動長時間運行查詢（全表掃描）
SELECT /*+ FULL(t) */ COUNT(*) FROM test t WHERE fid > 0;
-- 記住該查詢的 SQL_ID

-- 3. 會話 B：查詢執行中創建索引
CREATE INDEX idx_test_fid ON test(fid);

-- 4. 會話 A 查詢完成後，檢視執行計劃
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALL'));
-- 結果：仍顯示 TABLE ACCESS FULL，未使用新索引

-- 5. 會話 C：新會話執行相同 SQL
SELECT /*+ FULL(t) */ COUNT(*) FROM test t WHERE fid > 0;
-- 檢視計劃：可能改為 INDEX RANGE SCAN (視優化器判斷)

機制原理

1. 解析階段：SQL 進入庫緩存，優化器基於當前統計信息、索引元數據生成計劃，生成子游標；
2. 執行階段：遵循已確定計劃，不再重新優化；
3. DDL 失效機制：CREATE INDEX 會使相關 SQL 失效（V$SQL.INVALIDATIONS+1），但僅影響後續解析，不中斷正在執行的游標。

實戰啟示

• 在線建索引安全：CREATE INDEX ... ONLINE 期間，現有查詢繼續按舊計劃運行，業務零影響；
• 計劃變更窗口：新索引對新解析生效，若需立即生效可 DBMS_SHARED_POOL.PURGE 強制硬解析（謹慎使用）；
• 長運行查詢保護：報表、ETL 長查詢不會因中途建索引而計劃突變導致性能抖動；
• 統計信息同步：建索引後同步收集統計 DBMS_STATS.GATHER_TABLE_STATS(..., CASCADE=>TRUE)，確保新會話獲得最優計劃。

擴展驗證：其他 DDL 影響

理解「解析時綁定、執行時不變」的游標生命週期機制，是掌握 Oracle 並發 DML/DDL 安全共存的核心鑰匙。