在日常業(yè)務中，避免不了為數據量大表補充創(chuàng)建索引的情況，如果快速、有效地創(chuàng)建索引成了一個至關重要的問題（注意：雖然提供有ONLINE在線執(zhí)行的方式，理想狀態(tài)下不會阻塞DML操作，但ONLINE在開始、結束的兩個時刻仍然會產生獨占鎖，只是中間執(zhí)行過程中才以共享鎖的模式掃描表，建議還是在業(yè)務低峰期操作，避免在執(zhí)行窗口期高并發(fā)造成死鎖）。但對于超大量的，如TB級別的表，建議重新新建一個表，創(chuàng)建對應索引，將數據遷移，最后變更表名處理，不建議在原表上直接操作。

ONLINE 索引創(chuàng)建的內部簡化流程

準備階段 (非常短暫)

• 對表施加一個低級別的獨占鎖（TM 鎖，模式為 SSX）以準備構建工作。這個鎖允許其他會話進行查詢(SELECT)和大部分DML操作，但會阻止其他DDL操作（如另一個CREATE INDEX或ALTER TABLE）。這個階段非?？?。

掃描和構建階段 (主要耗時階段)

這是 ONLINE 的關鍵：Oracle 以共享模式 (S鎖) 掃描表。共享鎖與DML操作的排他鎖(X鎖)是兼容的。這意味著：

• 會話A可以持有共享鎖來掃描表以構建索引。
• 會話B可以同時持有排他鎖來更新某一行。
• 在此階段，Oracle會創(chuàng)建一個臨時日志表（Journal Table），用于記錄在索引構建開始后發(fā)生的、對相關數據的任何DML操作。

應用增量階段 (合并變更)

• 索引主體結構構建完成后，Oracle會讀取臨時日志表中的記錄，并將這些在構建期間發(fā)生的DML變更（增、刪、改）應用到新索引上。

最終切換階段 (非常短暫)

• 對新索引和表施加一個短暫的獨占鎖（X鎖），執(zhí)行一個原子操作，將新索引正式投入使用并使其對優(yōu)化器可見。這個鎖的持有時間極短，通常以毫秒計。

第一步：準備工作

除了預防死鎖，還應確保有足夠的資源（I/O、CPU） 來讓這個操作快速完成。

選擇維護窗口：

• 盡管是在線操作，但高并發(fā)期間仍會消耗大量CPU和I/O資源，可能影響業(yè)務性能。強烈建議在業(yè)務低峰期（如夜間、周末）執(zhí)行。

評估空間和估算大小：

-- 查看表當前占用空間，表空間不夠的話最好先增加表空間
SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_NAME = UPPER('<table>')
AND A.OWNER=UPPER('<owner>');

• 索引大小通常取決于索引列的長度和數量。您可以運行以下查詢進行粗略估算（將<table>替換為表名，<owner>替換為表用戶）：
• 根據表大小，為索引預留至少相當于表大小20%-30% 的額外表空間。

確定并行度 (PARALLEL)：

• 對于中上大小的數據量，像近6000萬的數據，使用并行非常有效。一個合理的起始點是服務器CPU核數的一半。
• 例如，如果服務器有16個CPU核心，可以從PARALLEL 8開始。
• 重要：創(chuàng)建完成后必須將并行度改回，否則會影響后續(xù)查詢的穩(wěn)定性。

決定是否使用NOLOGGING：

• NOLOGGING可以大幅提升速度，因為它幾乎不生成重做日志。
• 風險：如果索引創(chuàng)建后、下一次備份前數據庫發(fā)生故障，此索引可能會被標記為無效，需要重建。
• 建議：在維護窗口內，強烈建議使用NOLOGGING。完成后可以立即改回LOGGING模式。如果您的數據庫處于歸檔模式且備份策略完善，這個風險是可控的。

第二步：執(zhí)行腳本

將以下腳本中的占位符替換為您的實際信息：

• [INDEX_NAME]：新索引的名稱（如：IDX_XXXXXXX）
• [TABLE_NAME]：表名
• [COLUMN_LIST]：索引列（如：col1, col2）
• [TABLESPACE_NAME]：索引所在的表空間（可選，如果不指定則使用用戶的默認表空間）
• [PARALLEL_DEGREE]：并行度（如：8）

執(zhí)行腳本如下：

-- 1. 可選：開啟會話級并行，確保命令生效
ALTER SESSION ENABLE PARALLEL DDL;
 
-- 2. 核心：創(chuàng)建索引（ ONLINE 和 PARALLEL 是關鍵）
CREATE INDEX [OWNER.][INDEX_NAME] ON [OWNER.][TABLE_NAME] ([COLUMN_LIST])
TABLESPACE [TABLESPACE_NAME]  -- 可選，指定表空間
ONLINE                         -- 關鍵！允許并發(fā)DML，防止鎖等待和死鎖
PARALLEL [PARALLEL_DEGREE]     -- 關鍵！加速創(chuàng)建，例如 PARALLEL 8
NOLOGGING;                     -- 關鍵！大幅提升速度。評估風險后使用
 
-- 3. 創(chuàng)建完成后，立即將索引的并行度改回 1（或NONE），避免后續(xù)查詢過度并行
ALTER INDEX [OWNER.][INDEX_NAME] NOPARALLEL;
 
-- 4. 可選但建議：如果使用了NOLOGGING，將其改回LOGGING模式，確保后續(xù)變更被安全記錄
ALTER INDEX [OWNER.][INDEX_NAME] LOGGING;
 
-- 5. 收集新索引的統(tǒng)計信息（非常重要，否則優(yōu)化器無法有效使用索引）
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    OWNNAME => '[OWNER]',           -- 所屬用戶
    INDNAME => '[INDEX_NAME]',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE -- 讓ORACLE自動決定采樣比例
  );
END;
/

第三步：驗證

檢查索引狀態(tài)：

SELECT INDEX_NAME, STATUS, VISIBILITY
  FROM DBA_INDEXES A
 WHERE A.INDEX_NAME = UPPER('[INDEX_NAME]')
   AND A.OWNER = UPPER('[OWNER]');

• 確認 STATUS 為 VALID。
• 確認 VISIBILITY 為 VISIBLE（表示優(yōu)化器可以使用它）。

檢查索引段大小：

SELECT SEGMENT_NAME, BYTES / 1024 / 1024 AS SIZE_MB
  FROM DBA_SEGMENTS A
 WHERE A.SEGMENT_NAME = UPPER('[INDEX_NAME]')
   AND A.OWNER = UPPER('[OWNER]');

這可以讓你了解索引的實際大小。

到此這篇關于Oracle為數據大表創(chuàng)建索引的實現步驟的文章就介紹到這了,更多相關Oracle數據大表創(chuàng)建索引內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論