Oracle为数据大表创建索引的实现步骤
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">ONLINE 索引创建的内部简化流程</a></li><li><a href="#_label1">第一步：准备工作</a></li><li><a href="#_label2">第二步：执行脚本</a></li><li><a href="#_label3">第三步：验证</a></li></ul></div><p>在日常业务中，避免不了为数据量大表补充创建索引的情况，如果快速、有效地创建索引成了一个至关重要的问题（注意：虽然提供有ONLINE在线执行的方式，理想状态下不会阻塞DML操作，但ONLINE在开始、结束的两个时刻仍然会产生独占锁，只是中间执行过程中才以共享锁的模式扫描表，建议还是在业务低峰期操作，避免在执行窗口期高并发造成死锁）。但对于超大量的，如TB级别的表，建议重新新建一个表，创建对应索引，将数据迁移，最后变更表名处理，不建议在原表上直接操作。</p><p class="maodian"><a name="_label0"></a></p><h2>ONLINE 索引创建的内部简化流程</h2>
<p><strong>准备阶段 (非常短暂)</strong></p>
<ul><li>对表施加一个低级别的独占锁（<code>TM</code> 锁，模式为 <code>SSX</code>）以准备构建工作。这个锁允许其他会话进行查询(<code>SELECT</code>)和大部分DML操作，但会阻止其他DDL操作（如另一个<code>CREATE INDEX</code>或<code>ALTER TABLE</code>）。这个阶段非常快。</li></ul>
<p><strong>扫描和构建阶段 (主要耗时阶段)</strong></p>
<p><strong>这是 <code>ONLINE</code> 的关键</strong>：Oracle 以<strong>共享模式 (S锁)</strong> 扫描表。共享锁与DML操作的排他锁(X锁)是<strong>兼容的</strong>。这意味着：</p>
<ul><li>会话A可以持有共享锁来扫描表以构建索引。</li><li>会话B可以同时持有排他锁来更新某一行。</li><li>在此阶段，Oracle会创建一个<strong>临时日志表（Journal Table）</strong>，用于记录在索引构建开始后发生的、对相关数据的任何DML操作。</li></ul>
<p><strong>应用增量阶段 (合并变更)</strong></p>
<ul><li>索引主体结构构建完成后，Oracle会读取临时日志表中的记录，并将这些在构建期间发生的DML变更（增、删、改）应用到新索引上。</li></ul>
<p><strong>最终切换阶段 (非常短暂)</strong></p>
<ul><li>对新索引和表施加一个<strong>短暂的独占锁（X锁）</strong>，执行一个原子操作，将新索引正式投入使用并使其对优化器可见。这个锁的持有时间极短，通常以毫秒计。</li></ul>
<p class="maodian"><a name="_label1"></a></p><h2>第一步：准备工作</h2>
<p>除了预防死锁，还应<strong>确保有足够的资源（I/O、CPU）</strong> 来让这个操作快速完成。</p>
<p><strong>选择维护窗口</strong>：</p>
<ul><li>尽管是在线操作，但高并发期间仍会消耗大量CPU和I/O资源，可能影响业务性能。<strong>强烈建议在业务低峰期（如夜间、周末）执行</strong>。</li></ul>
<p><strong>评估空间和估算大小</strong>：</p>
<div class="jb51code"><pre class="brush: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>');</pre></div>
<ul><li>索引大小通常取决于索引列的长度和数量。您可以运行以下查询进行粗略估算（将<code><table></code>替换为表名，<owner>替换为表用户）：</li><li>根据表大小，为索引预留<strong>至少相当于表大小20%-30%</strong> 的额外表空间。</li></ul>
<p><strong>确定并行度 (PARALLEL)</strong>：</p>
<ul><li>对于中上大小的数据量，像近6000万的数据，使用并行非常有效。一个合理的起始点是<strong>服务器CPU核数的一半</strong>。</li><li>例如，如果服务器有16个CPU核心，可以从<code>PARALLEL 8</code>开始。</li><li><strong>重要</strong>：创建完成后必须将并行度改回，否则会影响后续查询的稳定性。</li></ul>
<p><strong>决定是否使用NOLOGGING</strong>：</p>
<ul><li><code>NOLOGGING</code>可以大幅提升速度，因为它几乎不生成重做日志。</li><li><strong>风险</strong>：如果索引创建后、下一次备份前数据库发生故障，此索引可能会被标记为无效，需要重建。</li><li><strong>建议</strong>：<strong>在维护窗口内，强烈建议使用<code>NOLOGGING</code></strong>。完成后可以立即改回<code>LOGGING</code>模式。如果您的数据库处于归档模式且备份策略完善，这个风险是可控的。</li></ul>
<p class="maodian"><a name="_label2"></a></p><h2>第二步：执行脚本</h2>
<p>将以下脚本中的占位符替换为您的实际信息：</p>
<ul><li><code></code>：新索引的名称（如：<code>IDX_XXXXXXX</code>）</li><li><code></code>：表名</li><li><code></code>：索引列（如：<code>col1, col2</code>）</li><li><code></code>：索引所在的表空间（可选，如果不指定则使用用户的默认表空间）</li><li><code></code>：并行度（如：<code>8</code>）</li></ul>
<p><strong>执行脚本如下：</strong></p>
<div class="jb51code"><pre class="brush:sql;">-- 1. 可选：开启会话级并行，确保命令生效
ALTER SESSION ENABLE PARALLEL DDL;
-- 2. 核心：创建索引（ ONLINE 和 PARALLEL 是关键）
CREATE INDEX ON ()
TABLESPACE -- 可选，指定表空间
ONLINE -- 关键！允许并发DML，防止锁等待和死锁
PARALLEL -- 关键！加速创建，例如 PARALLEL 8
NOLOGGING; -- 关键！大幅提升速度。评估风险后使用
-- 3. 创建完成后，立即将索引的并行度改回 1（或NONE），避免后续查询过度并行
ALTER INDEX NOPARALLEL;
-- 4. 可选但建议：如果使用了NOLOGGING，将其改回LOGGING模式，确保后续变更被安全记录
ALTER INDEX LOGGING;
-- 5. 收集新索引的统计信息（非常重要，否则优化器无法有效使用索引）
BEGIN
DBMS_STATS.GATHER_INDEX_STATS(
OWNNAME => '', -- 所属用户
INDNAME => '',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE -- 让ORACLE自动决定采样比例
);
END;
/</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>第三步：验证</h2>
<p><strong>检查索引状态</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT INDEX_NAME, STATUS, VISIBILITY
FROM DBA_INDEXES A
WHERE A.INDEX_NAME = UPPER('')
AND A.OWNER = UPPER('');</pre></div>
<ul><li>确认 <code>STATUS</code> 为 <strong>VALID</strong>。</li><li>确认 <code>VISIBILITY</code> 为 <strong>VISIBLE</strong>（表示优化器可以使用它）。</li></ul>
<p><strong>检查索引段大小</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT SEGMENT_NAME, BYTES / 1024 / 1024 AS SIZE_MB
FROM DBA_SEGMENTS A
WHERE A.SEGMENT_NAME = UPPER('')
AND A.OWNER = UPPER('');</pre></div>
<p>这可以让你了解索引的实际大小。</p>
頁:
[1]