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

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

準(zhǔn)備階段 (非常短暫)

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

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

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

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

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

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

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

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

第一步：準(zhǔn)備工作

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

選擇維護(hù)窗口：

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

評估空間和估算大小：

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

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

確定并行度 (PARALLEL)：

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

決定是否使用NOLOGGING：

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

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

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

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

執(zhí)行腳本如下：

-- 1. 可選：開啟會話級并行，確保命令生效
ALTER SESSION ENABLE PARALLEL DDL;
 
-- 2. 核心：創(chuàng)建索引（ ONLINE 和 PARALLEL 是關(guān)鍵）
CREATE INDEX [OWNER.][INDEX_NAME] ON [OWNER.][TABLE_NAME] ([COLUMN_LIST])
TABLESPACE [TABLESPACE_NAME]  -- 可選，指定表空間
ONLINE                         -- 關(guān)鍵！允許并發(fā)DML，防止鎖等待和死鎖
PARALLEL [PARALLEL_DEGREE]     -- 關(guān)鍵！加速創(chuàng)建，例如 PARALLEL 8
NOLOGGING;                     -- 關(guān)鍵！大幅提升速度。評估風(fēng)險后使用
 
-- 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]');

• 確認(rèn) STATUS 為 VALID。
• 確認(rèn) 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]');

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

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

最新評論