企业官网建设流程全解析

一、问题背景

测试表由 500 万行随机数据构成：

CREATETABLET1ASSELECTLEVELV1,DBMS_RANDOM.STRING('X',20)V2FROMDUALCONNECTBYLEVEL<=5000000;COMMIT;

数据检查结果如下：

TOTAL_ROWS = 5000000 MIN(V1) = 1 MAX(V1) = 5000000 V2 长度 = 20 COUNT(DISTINCT V1) = 5000000

需要优化的 SQL 是：

SELECT*FROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';

题目提出两个问题：

1. 如何让这条 SQL 变快，最快可以达到什么程度？
2. 执行计划里的5000000->256是什么，如何让256变成99？

最终实测结果可以先概括为：

以原始基线和最终 99 行场景进行直观比较，执行时间约缩短97.85%，约为原来的46.49 倍；逻辑读约下降99.58%。不过需要注意，两者返回行数不同，因此该倍数用于说明访问路径的改善，不应当包装成严格同口径的平均性能基准。

二、基线执行计划：为什么会扫描 500 万行

先开启 SQL 执行监控和跟踪：

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);SETAUTOTRACE TRACE;

原始 SQL 在没有业务索引时返回零行，关键执行计划如下：

#HASH2 INNER JOIN #SLCT2: B.V2 = '1' #CSCN2: [625, 5000000->0, 52] #CSCN2: [577, 5000000->256, 52]

对应的执行统计为：

15060 logical reads 222.407 exec time(ms) 0 rows returned

几个关键操作符的含义如下：

• CSCN2：聚集索引全扫描。这里本质上仍然要遍历表中的大量数据。
• SLCT2：对扫描结果应用B.V2='1'过滤条件。
• HASH2 INNER JOIN：以A.V1=B.V1为连接键进行哈希连接。
• 5000000->0：输入规模为 500 万行，实际输出为 0 行。
• 5000000->256：扫描节点从 500 万行对象中向上层执行器预取了一个批次的 256 行。

图 1 基线计划中一侧全表扫描得到零行，另一侧出现一次 256 行批量预取。

256不是查询返回行数

这是本题最容易混淆的地方。

5000000->256里的256并不表示 SQL 返回了 256 行，也不表示表中存在 256 条V2='1'的记录。它是执行器内部一次 BDTA 批量获取的数据量。由于连接另一侧最终为空，已经预取的这一批数据不会产生结果，所以客户端仍显示“未选定行”。

因此，下列两件事完全不同：

• 把数据改成恰好有 99 条V2='1'，使 SQL 返回 99 行；
• 把执行器一次批量预取大小从 256 改成 99。

前者是数据基数与 SQL 优化问题，后者是执行器静态参数实验。

三、第一阶段：单列索引和组合索引

1. 为什么需要两个索引

原始 SQL 有两个关键访问条件：

B.V2 = '1' A.V1 = B.V1

相应的第一版索引设计为：

CREATEINDEXIDX_T1_V1ONT1(V1);CREATEINDEXIDX_T1_V2_V1ONT1(V2,V1);

两个索引的职责不同：

• IDX_T1_V1支持按连接键V1回查匹配记录；
• IDX_T1_V2_V1以过滤列V2为首列，可以直接定位V2='1'的索引范围；同时索引中已经包含连接列V1，避免先扫描整表再过滤。

(V2,V1)的列顺序很重要。如果只建(V1,V2)，那么查询没有给出V1的固定前导条件，难以直接利用组合索引快速定位全部V2='1'记录。

下面的单列查询对比说明了V1索引如何把CSCN2全扫描改为SSEK2索引扫描：

图 2 创建IDX_T1_V1后，V1=1从全表扫描过滤变为索引范围扫描和回表。

2. 收集统计信息

索引创建后需要重新收集统计信息，使优化器了解表规模、列基数和索引选择性：

CALLSYS.DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T1',NULL,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

随后清理计划缓存并重新执行 SQL：

CALLSP_CLEAR_PLAN_CACHE();SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);SETAUTOTRACE TRACE;SELECT*FROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';

此时仍然没有V2='1'的记录，但执行统计已经下降到：

36 logical reads 2.688 exec time(ms)

计划中出现了：

#SSEK2: IDX_T1_V2_V1, scan_range[('1',min),('1',max)] #SSEK2: IDX_T1_V1, scan_range[B.V1,B.V1]

这说明数据库不再依赖对 T1 的完整数据扫描来判断结果为空，而是先在组合索引中查找V2='1'的范围。

图 3 逗号连接写法下，组合索引直接判断V2='1'不存在，逻辑读降到 36。

将 SQL 改写为 ANSI JOIN：

SELECT*FROMT1 AJOINT1 BONA.V1=B.V1WHEREB.V2='1';

得到的核心访问路径与逗号连接一致：

图 4 ANSI JOIN 与逗号连接在本例中生成等价的索引访问路径。改变书写风格本身不是性能优化，真正起作用的是索引、唯一性和统计信息。

四、第二阶段：构造 99 条目标数据

为了验证非空场景，并使查询确实返回 99 行，将V1=1~99的记录更新为V2='1'：

UPDATET1SETV2='1'WHEREV1BETWEEN1AND99;COMMIT;

验证数据基数：

SELECTCOUNT(*)ASV2_1_ROWSFROMT1WHEREV2='1';SELECTCOUNT(*)ASJOIN_ROWSFROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';

两条语句都返回：

99

这里的 99 是真实业务结果行数，而不是执行器批量大小。

五、用唯一性帮助优化器消除冗余自连接

原始数据已经证明：

COUNT(*) = 5000000 COUNT(DISTINCT V1)= 5000000

即V1在数据上具有唯一性。此前创建的普通索引没有把该约束信息告诉优化器，因此将其替换为唯一索引：

DROPINDEXIDX_T1_V1;CREATEUNIQUEINDEXIDX_T1_V1ONT1(V1);

然后重新收集统计信息，并为高基数的V2提供更细的直方图：

CALLSYS.DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T1',NULL,100,FALSE,'FOR COLUMNS V1 SIZE 1, V2 SIZE 10000',1,'AUTO',TRUE);

DBMS_STATS.GATHER_TABLE_STATS的参数签名可能随 DM8 版本有所差异，实际使用前应以当前版本手册和DESC结果为准。

为什么唯一索引能进一步优化

由于 A 和 B 都是同一张表，且V1唯一，因此对于任意一条 B 记录，满足A.V1=B.V1的 A 记录最多只有一条，并且就是具有同一V1的那条记录。优化器可以利用这个确定性，将原先的自连接语义化简为对目标记录的直接读取和投影。

最终实测计划为：

#NSET2: [1, 1->99, 64] #PRJT2: [1, 1->99, 64] #SLCT2: [1, 1->99, 64]; NOT(B.V1 IS NULL) #SSEK2: [1, 1->99, 64]; IDX_T1_V2_V1(T1) scan_range[('1',min),('1',max)]

这里已经看不到原先实际执行的HASH2 INNER JOIN和双侧CSCN2。运行时主要通过(V2,V1)组合索引定位 99 条记录，索引同时包含查询需要的两列，因此能够形成覆盖访问。

最终统计为：

99 rows got 64 logical reads 4.784 exec time(ms)

对“消除自连接”的准确表述

可以说优化器利用V1的唯一性完成了自连接化简，但不应把它泛化为“只要创建索引，所有自连接都会被消除”。能否化简取决于：

• 连接双方是否为同一关系；
• 连接列是否具有可信的唯一性；
• 过滤条件和投影列是否允许等价替换；
• 统计信息是否足够准确；
• 当前 DM8 版本的优化器规则是否支持该变换。

六、性能改善到底有多大

1. 原始零行与索引零行的同口径比较

这组对比的返回行数相同，更能直接证明索引访问路径避免了无效扫描。

2. 原始基线与最终 99 行场景的效果比较

最终方案实际返回了 99 行，并向客户端传输了更多结果数据，但执行时间仍保持在约 5 ms。这说明主要性能收益来自：

1. 组合索引直接定位低选择性结果范围；
2. 唯一索引向优化器提供了确定的唯一性；
3. 统计信息让优化器能够正确估计V2='1'的基数；
4. 自连接被语义化简，避免了不必要的哈希构建和大范围扫描。

这些数字来自单次实测，容易受到缓存、并发负载和硬件状态影响。正式性能报告应进行预热、多轮重复和分位数统计，不应只用一次耗时作为生产 SLA。

七、第二问：怎样把执行计划中的 256 变成 99？

如果老师指的是截图中被框出的：

#CSCN2: [577, 5000000->256, 52]

那么严格答案不是“构造 99 条数据”，而是调整 DM8 的静态参数BDTA_SIZE。

先查询当前参数：

SELECTPARA_NAME,PARA_VALUE,FILE_VALUE,PARA_TYPEFROMV$DM_INIWHEREPARA_NAME='BDTA_SIZE';

常见默认值为：

PARA_VALUE = 256 FILE_VALUE = 256 PARA_TYPE = IN FILE

在隔离测试实例中修改参数：

ALTERSYSTEMSET'BDTA_SIZE'=99SPFILE;

由于这是静态参数，需要重启测试实例后才能使内存值同步为 99。重启后再次确认：

SELECTPARA_NAME,PARA_VALUE,FILE_VALUEFROMV$DM_INIWHEREPARA_NAME='BDTA_SIZE';

理论上，在仍保留旧版执行器预取行为的 DM8 版本中，重新运行原始 SQL 后可观察到：

修改前：#CSCN2: [..., 5000000->256, ...] 修改后：#CSCN2: [..., 5000000->99, ...]

这不是 SQL 性能优化

把批量预取从 256 改成 99，只是改变执行器一次取数的批量大小。它并没有：

• 减少表中的 500 万行；
• 为V2='1'建立有效访问路径；
• 消除全表扫描；
• 保证整体吞吐量或响应时间更好。

批量过小可能增加执行器调用次数，批量过大则可能增加单批内存占用。因此不应仅为了让执行计划显示一个特定数字，就在生产系统修改该参数。

实验结束后应恢复默认值并重启隔离实例：

ALTERSYSTEMSET'BDTA_SIZE'=256SPFILE;

八、为什么当前可能看不到->99

在 DM8 引擎上复测相同的空结果哈希连接时，执行计划出现了不同的运行行为：

#SLCT2: B.V2='1' #CSCN2: [670, 5000000->0, 96]; n_enter:1 #CSCN2: [622, 5000000, 96]; n_enter:0

n_enter:0表示当引擎确认哈希连接的一侧为空后，另一侧扫描节点根本没有被进入。新版执行器直接进行了空分支短路，因此既不会预取 256 行，也不会在将BDTA_SIZE改成 99 后显示5000000->99。

这不是实验失败，而是版本行为变化：

• 旧执行器可能先从另一侧预取一个 BDTA 批次，再发现连接结果为空；
• 新执行器先确认构建侧为空，然后直接跳过探测侧。

九、对原提交方案的评价

原提交思路是：

构造 99 条V2='1'的测试数据，在V1上建立唯一索引、在(V2,V1)上建立组合索引并重新收集统计信息，使优化器利用V1的唯一性化简冗余自连接，同时通过SSEK2直接定位组合索引中满足V2='1'的 99 条记录，避免原来的CSCN2全表扫描。

这个方案对“怎样让 SQL 变快”是成立的，而且有完整实测证据：

• 查询确实返回 99 行；
• 最终计划主要使用SSEK2；
• 哈希自连接和全表扫描不再执行；
• 逻辑读和响应时间显著下降。

但它不能单独回答“怎样让5000000->256中的 256 变成 99”，因为最终计划已经发生结构变化，原来的CSCN2节点不再实际执行。更准确的答题方式是：

1. 用索引、唯一性和统计信息回答性能优化问题；
2. 用BDTA_SIZE=99回答执行器批量大小问题；
3. 明确说明两种 99 的语义不同。

十、完整可复现实验脚本

以下脚本适合在独立测试库中执行。它会创建 500 万行数据并建立索引，执行前应确认表名不会覆盖已有对象。

-- 1. 创建测试表CREATETABLET1ASSELECTLEVELV1,DBMS_RANDOM.STRING('X',20)V2FROMDUALCONNECTBYLEVEL<=5000000;COMMIT;-- 2. 验证规模和 V1 唯一性SELECTCOUNT(*)ASTOTAL_ROWS,COUNT(DISTINCTV1)ASDISTINCT_V1FROMT1;-- 3. 执行基线 SQLSF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);SETAUTOTRACE TRACE;SELECT*FROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';SETAUTOTRACEOFF;-- 4. 创建索引CREATEUNIQUEINDEXIDX_T1_V1ONT1(V1);CREATEINDEXIDX_T1_V2_V1ONT1(V2,V1);-- 5. 构造 99 条目标数据UPDATET1SETV2='1'WHEREV1BETWEEN1AND99;COMMIT;-- 6. 收集统计信息CALLSYS.DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','T1',NULL,100,FALSE,'FOR COLUMNS V1 SIZE 1, V2 SIZE 10000',1,'AUTO',TRUE);CALLSP_CLEAR_PLAN_CACHE();-- 7. 验证数据和连接结果SELECTCOUNT(*)FROMT1WHEREV2='1';SELECTCOUNT(*)FROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';-- 8. 查看优化后的真实执行计划SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);SETAUTOTRACE TRACE;SELECT*FROMT1 A,T1 BWHEREA.V1=B.V1ANDB.V2='1';SETAUTOTRACEOFF;

BDTA_SIZE实验应与索引优化实验分开进行：

-- 仅限隔离测试实例SELECTPARA_NAME,PARA_VALUE,FILE_VALUE,PARA_TYPEFROMV$DM_INIWHEREPARA_NAME='BDTA_SIZE';ALTERSYSTEMSET'BDTA_SIZE'=99SPFILE;-- 重启隔离实例后，重新连接并复测原 SQL-- 实验结束后恢复ALTERSYSTEMSET'BDTA_SIZE'=256SPFILE;-- 再次重启隔离实例并确认 PARA_VALUE、FILE_VALUE 均为 256

十一、总结

这道题的价值不只是“建一个索引”，而是训练我们准确阅读真实执行计划：

• CSCN2暴露了大范围扫描；
• (V2,V1)组合索引把过滤列放在前面，并覆盖连接列；
• V1唯一索引既支持查找，也向优化器提供了可用于语义化简的约束信息；
• 重新收集统计信息后，最终计划通过SSEK2返回 99 行；
• 实测从222.407 ms / 15060 logical reads改善到4.784 ms / 64 logical reads；
• 5000000 → 256中的 256 是 BDTA 批量预取大小，不是查询结果数；
• 构造 99 条记录和设置BATCH_SIZE=99是两种完全不同的实验；
• 新版引擎可能直接短路空连接分支，因此复现旧计划必须考虑版本差异。