Oracle为数据大表创建索引方案-CSDN博客

在日常业务中，避免不了为数据量大表补充创建索引的情况，如果快速、有效地创建索引成了一个至关重要的问题（注意：虽然提供有ONLINE在线执行的方式，理想状态下不会阻塞DML操作，但ONLINE在开始、结束的两个时刻仍然会产生独占锁，只是中间执行过程中才以共享锁的模式扫描表，建议还是在业务低峰期操作，避免在执行窗口期高并发造成死锁）。但对于超大量的，如TB级别的表，建议重新新建一个表，创建对应索引，将数据迁移，最后变更表名处理，不建议在原表上直接操作。

ONLINE 索引创建的内部简化流程

准备阶段 (非常短暂)
- 对表施加一个低级别的独占锁（TM 锁，模式为 SSX）以准备构建工作。这个锁允许其他会话进行查询(SELECT)和大部分DML操作，但会阻止其他DDL操作（如另一个CREATE INDEX或ALTER TABLE）。这个阶段非常快。
扫描和构建阶段 (主要耗时阶段)
- 这是 ONLINE 的关键：Oracle 以共享模式 (S锁) 扫描表。共享锁与DML操作的排他锁(X锁)是兼容的。这意味着：
  - 会话A可以持有共享锁来扫描表以构建索引。
  - 会话B可以同时持有排他锁来更新某一行。
- 在此阶段，Oracle会创建一个临时日志表（Journal Table），用于记录在索引构建开始后发生的、对相关数据的任何DML操作。
应用增量阶段 (合并变更)
- 索引主体结构构建完成后，Oracle会读取临时日志表中的记录，并将这些在构建期间发生的DML变更（增、删、改）应用到新索引上。
最终切换阶段 (非常短暂)
- 对新索引和表施加一个短暂的独占锁（X锁），执行一个原子操作，将新索引正式投入使用并使其对优化器可见。这个锁的持有时间极短，通常以毫秒计。

第一步：准备工作

除了预防死锁，还应确保有足够的资源（I/O、CPU） 来让这个操作快速完成。

选择维护窗口：
- 尽管是在线操作，但高并发期间仍会消耗大量CPU和I/O资源，可能影响业务性能。强烈建议在业务低峰期（如夜间、周末）执行。
评估空间和估算大小：
- 索引大小通常取决于索引列的长度和数量。您可以运行以下查询进行粗略估算（将<table>替换为表名，<owner>替换为表用户）：
sql
```
-- 查看表当前占用空间，表空间不够的话最好先增加表空间
SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_NAME = UPPER('<table>')
AND A.OWNER=UPPER('<owner>');
```
- 根据表大小，为索引预留至少相当于表大小20%-30% 的额外表空间。
确定并行度 (PARALLEL)：
- 对于中上大小的数据量，像近6000万的数据，使用并行非常有效。一个合理的起始点是服务器CPU核数的一半。
- 例如，如果服务器有16个CPU核心，可以从PARALLEL 8开始。
- 重要：创建完成后必须将并行度改回，否则会影响后续查询的稳定性。
决定是否使用NOLOGGING：
- NOLOGGING可以大幅提升速度，因为它几乎不生成重做日志。
- 风险：如果索引创建后、下一次备份前数据库发生故障，此索引可能会被标记为无效，需要重建。
- 建议：在维护窗口内，强烈建议使用NOLOGGING。完成后可以立即改回LOGGING模式。如果您的数据库处于归档模式且备份策略完善，这个风险是可控的。

第二步：执行脚本

将以下脚本中的占位符替换为您的实际信息：

[INDEX_NAME]：新索引的名称（如：IDX_XXXXXXX）
[TABLE_NAME]：表名
[COLUMN_LIST]：索引列（如：col1, col2）
[TABLESPACE_NAME]：索引所在的表空间（可选，如果不指定则使用用户的默认表空间）
[PARALLEL_DEGREE]：并行度（如：8）

执行脚本如下：

sql

-- 1. 可选：开启会话级并行，确保命令生效
ALTER SESSION ENABLE PARALLEL DDL;

-- 2. 核心：创建索引（ ONLINE 和 PARALLEL 是关键）
CREATE INDEX [OWNER.][INDEX_NAME] ON [OWNER.][TABLE_NAME] ([COLUMN_LIST])
TABLESPACE [TABLESPACE_NAME]  -- 可选，指定表空间
ONLINE                         -- 关键！允许并发DML，防止锁等待和死锁
PARALLEL [PARALLEL_DEGREE]     -- 关键！加速创建，例如 PARALLEL 8
NOLOGGING;                     -- 关键！大幅提升速度。评估风险后使用

-- 3. 创建完成后，立即将索引的并行度改回 1（或NONE），避免后续查询过度并行
ALTER INDEX [OWNER.][INDEX_NAME] NOPARALLEL;

-- 4. 可选但建议：如果使用了NOLOGGING，将其改回LOGGING模式，确保后续变更被安全记录
ALTER INDEX [OWNER.][INDEX_NAME] LOGGING;

-- 5. 收集新索引的统计信息（非常重要，否则优化器无法有效使用索引）
BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(
    OWNNAME => '[OWNER]',           -- 所属用户
    INDNAME => '[INDEX_NAME]',
    ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE -- 让ORACLE自动决定采样比例
  );
END;
/

第三步：验证

检查索引状态：

sql

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（表示优化器可以使用它）。

检查索引段大小：

sql

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]');

这可以让你了解索引的实际大小。