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

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

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

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

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

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

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

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

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

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

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

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

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

選擇維護(hù)窗口：

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

評(píng)估空間和估算大小：

-- 查看表當(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>');

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

確定并行度 (PARALLEL)：

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

決定是否使用NOLOGGING：

• NOLOGGING可以大幅提升速度，因?yàn)樗鼛缀醪簧芍刈鋈罩尽?/li>
• 風(fēng)險(xiǎn)：如果索引創(chuàng)建后、下一次備份前數(shù)據(jù)庫(kù)發(fā)生故障，此索引可能會(huì)被標(biāo)記為無效，需要重建。
• 建議：在維護(hù)窗口內(nèi)，強(qiáng)烈建議使用NOLOGGING。完成后可以立即改回LOGGING模式。如果您的數(shù)據(jù)庫(kù)處于歸檔模式且備份策略完善，這個(gè)風(fēng)險(xiǎn)是可控的。

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

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

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

執(zhí)行腳本如下：

-- 1. 可選：開啟會(huì)話級(jí)并行，確保命令生效
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)鍵！大幅提升速度。評(píng)估風(fēng)險(xiǎn)后使用
 
-- 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)計(jì)信息（非常重要，否則優(yōu)化器無法有效使用索引）
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    OWNNAME => '[OWNER]',           -- 所屬用戶
    INDNAME => '[INDEX_NAME]',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE -- 讓ORACLE自動(dòng)決定采樣比例
  );
END;
/

第三步：驗(yàn)證

檢查索引狀態(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]');

這可以讓你了解索引的實(shí)際大小。

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

最新評(píng)論