DM8 五百万行自连接 SQL 优化

一、问题背景

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

CREATE TABLE T1 AS
SELECT LEVEL V1,
       DBMS_RANDOM.STRING('X', 20) V2
FROM DUAL
CONNECT BY LEVEL <= 5000000;

COMMIT;

数据检查结果如下：

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

需要优化的 SQL 是：

SELECT *
FROM T1 A, T1 B
WHERE A.V1 = B.V1
  AND B.V2 = '1';

题目提出两个问题：

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

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

测试场景	返回行数	logical reads	exec time
无业务索引，`V2='1'` 为零行	0	15060	222.407 ms
建立普通索引和组合索引，仍为零行	0	36	2.688 ms
构造 99 行并使用唯一索引、组合覆盖索引	99	64	4.784 ms

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

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

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

SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);
SET AUTOTRACE 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

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

CREATE INDEX IDX_T1_V1
ON T1(V1);

CREATE INDEX IDX_T1_V2_V1
ON T1(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. 收集统计信息

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

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

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

CALL SP_CLEAR_PLAN_CACHE();
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC', 1);
SET AUTOTRACE TRACE;

SELECT *
FROM T1 A, T1 B
WHERE A.V1 = B.V1
  AND B.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 *
FROM T1 A
JOIN T1 B
  ON A.V1 = B.V1
WHERE B.V2 = '1';

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

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

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

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

UPDATE T1
SET V2 = '1'
WHERE V1 BETWEEN 1 AND 99;

COMMIT;

验证数据基数：

SELECT COUNT(*) AS V2_1_ROWS
FROM T1
WHERE V2 = '1';

SELECT COUNT(*) AS JOIN_ROWS
FROM T1 A, T1 B
WHERE A.V1 = B.V1
  AND B.V2 = '1';

两条语句都返回：

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

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

原始数据已经证明：

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

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

DROP INDEX IDX_T1_V1;

CREATE UNIQUE INDEX IDX_T1_V1
ON T1(V1);

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

CALL SYS.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. 原始零行与索引零行的同口径比较

指标	无业务索引	建立索引后	改善
返回行数	0	0	相同
logical reads	15060	36	减少约 99.76%
exec time	222.407 ms	2.688 ms	约 82.74 倍

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

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

指标	原始基线	最终方案	改善
返回行数	0	99	场景不同
logical reads	15060	64	减少约 99.58%
执行时间	222.407 ms	4.784 ms	提升约 46.49 倍

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

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

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

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

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

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

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

先查询当前参数：

SELECT PARA_NAME,
       PARA_VALUE,
       FILE_VALUE,
       PARA_TYPE
FROM V$DM_INI
WHERE PARA_NAME = 'BDTA_SIZE';

常见默认值为：

PARA_VALUE = 256
FILE_VALUE = 256
PARA_TYPE  = IN FILE

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

ALTER SYSTEM SET 'BDTA_SIZE'=99 SPFILE;

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

SELECT PARA_NAME, PARA_VALUE, FILE_VALUE
FROM V$DM_INI
WHERE PARA_NAME = 'BDTA_SIZE';

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

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

这不是 SQL 性能优化

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

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

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

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

ALTER SYSTEM SET 'BDTA_SIZE'=256 SPFILE;

八、为什么当前可能看不到 `->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 节点不再实际执行。更准确的答题方式是：

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

十、完整可复现实验脚本

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

-- 1. 创建测试表
CREATE TABLE T1 AS
SELECT LEVEL V1,
       DBMS_RANDOM.STRING('X',20) V2
FROM DUAL
CONNECT BY LEVEL <= 5000000;

COMMIT;

-- 2. 验证规模和 V1 唯一性
SELECT COUNT(*) AS TOTAL_ROWS,
       COUNT(DISTINCT V1) AS DISTINCT_V1
FROM T1;

-- 3. 执行基线 SQL
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
SET AUTOTRACE TRACE;

SELECT *
FROM T1 A, T1 B
WHERE A.V1=B.V1
  AND B.V2='1';

SET AUTOTRACE OFF;

-- 4. 创建索引
CREATE UNIQUE INDEX IDX_T1_V1
ON T1(V1);

CREATE INDEX IDX_T1_V2_V1
ON T1(V2,V1);

-- 5. 构造 99 条目标数据
UPDATE T1
SET V2='1'
WHERE V1 BETWEEN 1 AND 99;

COMMIT;

-- 6. 收集统计信息
CALL SYS.DBMS_STATS.GATHER_TABLE_STATS(
    'SYSDBA',
    'T1',
    NULL,
    100,
    FALSE,
    'FOR COLUMNS V1 SIZE 1, V2 SIZE 10000',
    1,
    'AUTO',
    TRUE
);

CALL SP_CLEAR_PLAN_CACHE();

-- 7. 验证数据和连接结果
SELECT COUNT(*) FROM T1 WHERE V2='1';

SELECT COUNT(*)
FROM T1 A, T1 B
WHERE A.V1=B.V1
  AND B.V2='1';

-- 8. 查看优化后的真实执行计划
SF_SET_SESSION_PARA_VALUE('MONITOR_SQL_EXEC',1);
SET AUTOTRACE TRACE;

SELECT *
FROM T1 A, T1 B
WHERE A.V1=B.V1
  AND B.V2='1';

SET AUTOTRACE OFF;

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

-- 仅限隔离测试实例
SELECT PARA_NAME, PARA_VALUE, FILE_VALUE, PARA_TYPE
FROM V$DM_INI
WHERE PARA_NAME='BDTA_SIZE';

ALTER SYSTEM SET 'BDTA_SIZE'=99 SPFILE;

-- 重启隔离实例后，重新连接并复测原 SQL

-- 实验结束后恢复
ALTER SYSTEM SET 'BDTA_SIZE'=256 SPFILE;

-- 再次重启隔离实例并确认 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 是两种完全不同的实验；
新版引擎可能直接短路空连接分支，因此复现旧计划必须考虑版本差异。