DM8 表连接方式与执行计划

表连接与执行计划基础
SQL 中的 INNER JOIN、LEFT JOIN、RIGHT JOIN 等描述的是查询结果应满足的逻辑关系；数据库真正执行 SQL 时，还需要选择具体的物理连接算法。DM8 优化器会结合表的数据量、过滤条件、索引、统计信息、连接列分布以及估算代价等因素，在多个候选执行计划中选择成本较低的方案。
1.1 HASH JOIN 哈希连接
HASH JOIN 主要用于等值连接。执行时通常选择一侧数据根据连接键构造哈希结构，另一侧数据再使用相同哈希规则进行探测，从而找到连接键相同的记录。其优势是对于大批量等值连接，可以避免反复扫描内表。

1.2 NEST LOOP 嵌套循环连接
NEST LOOP 可以理解为“外层结果驱动内层查找”。外侧每得到一行或一批连接键，就到内侧寻找匹配记录。因此它的效率与外侧结果集大小以及内侧访问路径密切相关。

1.3 索引参与的连接
索引参与连接通常与 NEST LOOP 配合。外侧先得到少量连接键，内侧使用连接列上的二级索引直接定位匹配范围，再根据需要回表读取其他列。此时索引的价值不在于存”，而在于能否显著减少内侧需要读取的数据。

1.4优化器选择 JOIN 的主要因素

表和过滤后结果集的数据量。
连接条件是等值还是非等值。
连接列及过滤列是否存在可用索引。
索引选择性以及通过索引后需要回表的记录数量。
统计信息是否能够准确反映行数和数据分布。
各候选计划的估算代价。

实验设计与测试数据
实验采用一张小表 T_DEPT 和一张大表 T_EMP。先在没有业务二级索引的情况下观察优化器自然生成的执行计划，再对 T_EMP(DEPT_ID) 建立索引，保持 SQL 不变进行前后对照。另使用数据倾斜明显的 FLAG 字段观察选择性对执行计划估算的影响。

CREATE TABLE T_DEPT
(
    DEPT_ID   INT,
    DEPT_NAME VARCHAR(50)
);

CREATE TABLE T_EMP
(
    EMP_ID    INT,
    DEPT_ID   INT,
    EMP_NAME  VARCHAR(50),
    SALARY    DECIMAL(10,2),
    FLAG      CHAR(1)
);
BEGIN
    FOR I IN 1..100 LOOP
        INSERT INTO T_DEPT VALUES(I, 'DEPT_' || I);
    END LOOP;
    COMMIT;
END;
/

BEGIN
    FOR I IN 1..100000 LOOP
        INSERT INTO T_EMP
        VALUES(
            I,
            MOD(I,100)+1,
            'EMP_' || I,
            5000 + MOD(I,20000),
            CASE WHEN MOD(I,1000)=0 THEN 'Y' ELSE 'N' END
        );
    END LOOP;
    COMMIT;
END;
/
SELECT COUNT(*) FROM T_DEPT;
SELECT COUNT(*) FROM T_EMP;

SELECT FLAG, COUNT(*)
FROM T_EMP
GROUP BY FLAG;

2.1 收集统计信息

STAT ON T_DEPT;
STAT ON T_EMP;
STAT 100 ON T_DEPT(DEPT_ID);
STAT 100 ON T_EMP(DEPT_ID);
STAT 100 ON T_EMP(FLAG);

实验 A：二级索引的全量连接

EXPLAIN
SELECT E.EMP_ID,
       E.EMP_NAME,
       D.DEPT_NAME
FROM T_EMP E
JOIN T_DEPT D
  ON E.DEPT_ID = D.DEPT_ID;

两侧都没有能够显著缩小读取范围的业务二级索引，而且该 SQL 需要返回约 100000 行结果。优化器因此选择扫描两侧后做 HASH 匹配。这种路线避免了大量重复的内表查找，符合大结果集等值连接的典型特征。

5. 全量 HASH JOIN 的实际耗时
SET TIMING ON;

SELECT COUNT(*)
FROM T_EMP E
JOIN T_DEPT D
ON E.DEPT_ID = D.DEPT_ID;

6. 实验 B：建立连接列索引后的全量连接

CREATE INDEX IDX_EMP_DEPT
ON T_EMP(DEPT_ID);

STAT 100 ON T_EMP(DEPT_ID);

SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'T_EMP';

这个结果说明索引是否被使用取决于总代价。虽然 T_EMP.DEPT_ID 上已经存在二级索引，但全量连接仍需要处理约 100000 行数据。若通过索引定位大量记录并频繁回表，成本未必低于直接扫描，因此优化器保留了原来的 HASH JOIN 路线。
7. 实验 C：单部门过滤，无 IDX_EMP_DEPT

DROP INDEX IDX_EMP_DEPT;
STAT ON T_EMP;

EXPLAIN
SELECT E.EMP_ID,
       E.EMP_NAME,
       D.DEPT_NAME
FROM T_DEPT D
JOIN T_EMP E
  ON E.DEPT_ID = D.DEPT_ID
WHERE D.DEPT_ID = 10;

这里可以看出，JOIN 算法已经从全量连接时的 HASH JOIN 变成 NEST LOOP，但内表 T_EMP 仍需要全表扫描，因此 NEST LOOP 本身并不代表一定高效。
8. 单部门连接：无索引耗时

SET TIMING ON;
SELECT COUNT(*)
FROM T_DEPT D
JOIN T_EMP E
  ON E.DEPT_ID = D.DEPT_ID
WHERE D.DEPT_ID = 10;

9. 实验 D：单部门过滤，建立 IDX_EMP_DEPT

CREATE INDEX IDX_EMP_DEPT
ON T_EMP(DEPT_ID);

STAT 100 ON T_EMP(DEPT_ID);

EXPLAIN
SELECT E.EMP_ID,
       E.EMP_NAME,
       D.DEPT_NAME
FROM T_DEPT D
JOIN T_EMP E
  ON E.DEPT_ID = D.DEPT_ID
WHERE D.DEPT_ID = 10;

建立二级索引后，JOIN 算法仍然是 NEST LOOP，真正变化的是 T_EMP 的访问路径。内表由全表扫描改为“二级索引范围定位 + 回表”，优化器估算代价从 25 降至 6。
10. 索引前后的执行路线与耗时对照
11.
该组实验的 SQL、数据量和返回行数保持一致，主要变量是 T_EMP.DEPT_ID 是否存在二级索引。索引建立后，优化器从“扫描 100000 行再过滤”变为“直接定位 DEPT_ID=10 的约 1000 行”，因此计划代价和实际耗时均明显下降。
11. 实验 E：数据选择性对访问路线的影响
T_EMP.FLAG 中 Y 只有 100 行，N 有 99900 行。通过对比两个条件，可以观察优化器对数据分布的估算以及最终访问路径。
11.1 FLAG='Y'：100 行
EXPLAIN
SELECT *
FROM T_EMP
WHERE FLAG = 'Y';

SET TIMING ON;
SELECT COUNT(*)
FROM T_EMP
WHERE FLAG = 'Y';

11.2 FLAG='N'：99900 行
EXPLAIN
SELECT *
FROM T_EMP
WHERE FLAG = 'N';

SELECT COUNT(*)
FROM T_EMP
WHERE FLAG = 'N';

11.3 结果分析
两个条件的估算行数分别为 100 和 99900，说明统计信息能够较准确反映 FLAG 的数据倾斜。但当前两个执行计划都选择 CSCN2 扫描后过滤，说明“过滤结果很少”只是优化器决策因素之一，最终路线仍取决于可用索引和总代价。

DM8 表连接方式与执行计划

作者