ORDER_BY_NULLS_FLAG升序索引消SORT
ORDER_BY_NULLS_FLAG升序索引消SORT
本文基于 DM8 与 Oracle 19.3 双侧对照实验，完整还原"升序索引 + ORDER BY ASC 无法消除 SORT，降序索引可消除"问题的现象、根因与规避方案。所有主机、端口、口令均为示例值，请按实际环境调整。
一、问题描述
现象：创建升序索引后，ORDER BY col ASC 仍无法消除 SORT；把索引改成降序后排序消除。Oracle 同场景使用 INDEX RANGE SCAN 直接出结果，无 SORT 算子。
测试反馈：设置 ORDER_BY_NULLS_FLAG=0（参数或 hint）即可让 DM 消除 SORT。
| 场景 | 是否有 SORT | 说明 |
|---|---|---|
| ① DM 默认参数 + 升序索引 + ORDER BY ASC | ✅ 有 SORT3 | 需额外排序搬 NULL |
② hint /*+ ORDER_BY_NULLS_FLAG(0) */ |
❌ 已消除 | hint 生效 |
③ 参数 SP_SET_PARA_VALUE(1,'ORDER_BY_NULLS_FLAG',0) |
❌ 已消除 | 参数生效 |
| ④ 降序索引（PRO_NUM DESC） | ❌ 已消除 | 反向扫 |
| Oracle 同 SQL | ❌ 无 SORT | INDEX RANGE SCAN |
二、根因分析（为什么 DM 多一个 SORT、Oracle 没有）
核心差异：NULL 值在 B+树索引中的位置不同，导致对"升序排序 + NULL 语义"的处理分叉：
| 对比项 | Oracle | DM（ORDER_BY_NULLS_FLAG=1，默认） |
|---|---|---|
| 升序索引中 NULL 存储位置 | 索引末尾（隐式 NULLS LAST） | 索引最前（NULL 视为最小） |
| ORDER BY col ASC 时 | 索引正扫 = NULL 自然在最后，天然匹配默认排序语义，无需 SORT | 索引正扫 = NULL 排最前，与"升序时 NULL 放最后"的兼容语义相反 → 引擎必须 SORT 调整 NULL 位置 |
| 结果 | INDEX RANGE SCAN，无排序 | SORT 算子，额外开销 |
即：ORDER_BY_NULLS_FLAG=1（默认）是"兼容 Oracle 语义"模式（查询结果 NULL 排最后），但达梦索引里 NULL 在最前，引擎为了语义正确只能加一层排序来搬 NULL。Oracle 索引里 NULL 本就在最后，天然省掉这步。
💡 为什么引擎"宁愿排序"也不直接走索引？ SQL 语义必须保证"ASC 时 NULL 在最后"（Oracle 兼容），而索引扫出来的顺序是 NULL 在前，直接返回就违反语义——优化器不能赌"这一列没 NULL"，所以必须加 SORT3 把 NULL 挪到结果末尾。这是结构性/声明性决策：FLAG=1 时，列是否 NOT NULL、数据是否真有 NULL，都不改变"必须排序"的判定。
三、达梦侧测试流程与实测结果
1. 环境参数确认
SELECT PARA_NAME, PARA_VALUE FROM V$DM_INI
WHERE PARA_NAME IN ('ORDER_BY_NULLS_FLAG','ENABLE_INJECT_HINT');
-- ORDER_BY_NULLS_FLAG = 1 （SESSION级，默认1：升序时NULL放结果集末尾=Oracle兼容语义）
-- ENABLE_INJECT_HINT = 0 （未开 hint 注入；但实测 ORDER_BY_NULLS_FLAG hint 仍生效，见 3.3）
2. 建表造数（bug 原文数据）
CREATE TABLE ASC_TEST1 AS SELECT LEVEL ID,LEVEL*2 ID_CARD,LEVEL*10 PRO_NUM FROM DUAL CONNECT BY LEVEL;
3. 四种方案执行计划对比（实测）
| 方案 | 执行计划 | 是否有 SORT |
|---|---|---|
| ① bug 复现：默认参数 + 升序索引 ORDER BY PRO_NUM ASC | SORT3: [1,2499,20]; key_num(1), is_distinct(FALSE) → SSEK2: IDX_ASC1, scan_range[(200,min),(200,max)) | ✅ 有 SORT3 |
② hint 方案 /*+ ORDER_BY_NULLS_FLAG(0) */ |
SSEK2: IDX_ASC1, scan_type(ASC), scan_range[(200,min),(200,max)) | ❌ 已消除，hint 生效（即使 ENABLE_INJECT_HINT=0） |
③ 参数方案 SP_SET_PARA_VALUE(1,'ORDER_BY_NULLS_FLAG',0) |
SSEK2: IDX_ASC1, scan_type(ASC), scan_range[(200,min),(200,max)) | ❌ 已消除，参数生效 |
| ④ 降序索引 PRO_NUM DESC | SSEK2: IDX_ASC1, scan_type(DESC), scan_range[(200,min),(200,max)) | ❌ 已消除，反向扫 |
实测耗时（10 万行，ID_CARD=200 仅命中 1 行，数据量小差异有限，但方向一致）：
- 带 SORT：12.45 ms｜hint 免 SORT：0.87 ms（约 14 倍）。真实业务数据量大（命中行多）时差异会放大到可感知水平。
四、Oracle 侧测试流程与实测结果
测试环境：Oracle Database 19c（19.3.0.0.0），表结构与 DM 侧完全同构（CONNECT BY LEVEL < 100000 造 99,999 行）。
1. 同构造数 + 单值等值查询（bug 原始场景）
CREATE TABLE ASC_TEST1 AS SELECT LEVEL ID,LEVEL*2 ID_CARD,LEVEL*10 PRO_NUM FROM DUAL CONNECT BY LEVEL < 100000;
CREATE INDEX IDX_ASC1 ON ASC_TEST1(ID_CARD,PRO_NUM); -- 升序索引（Oracle 默认）
SET AUTOTRACE ON
SELECT PRO_NUM FROM ASC_TEST1 WHERE ID_CARD = 200 ORDER BY PRO_NUM ASC;
Execution Plan -- 实测计划（与 bug 原文完全一致）
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0) | 00:00:01 |
|* 1 | INDEX RANGE SCAN | IDX_ASC1 | 1 | 26 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------------
Statistics -- autotrace 关键计数
0 sorts (memory)
0 sorts (disk)
1 rows processed
结论：Oracle 与 bug 原文一致——直接 INDEX RANGE SCAN 出结果，无 SORT 算子，sorts(memory/disk) 均为 0。反观 DM 默认参数下同为 IDX_ASC1 却挂上 SORT3。
2. 数据里插入真实 NULL（回答"数据有没有 null"）
INSERT INTO ASC_TEST1 VALUES (999999,200,NULL); -- ID_CARD=200, PRO_NUM 为 NULL
INSERT INTO ASC_TEST1 VALUES (999998,200,NULL); -- ID_CARD=200, PRO_NUM 为 NULL
- EXPLAIN PLAN：与 4.1 完全一致——INDEX RANGE SCAN，无 SORT。
- AUTOTRACE：0 sorts (memory) / 0 sorts (disk)，3 rows processed（含 2 个 NULL 行）。
💡 关键结论：数据里有没有 NULL，Oracle 都不需要排序——因为 Oracle 升序索引在物理结构上就是把 NULL 放在索引末尾（隐式 NULLS LAST），与"ASC 排序 NULL 在最后"的语义天然一致，不是优化器判断数据里没 NULL 才免排（数据里明明有 NULL 也一样 0 sorts）。
对比 DM：DM 侧实测证明 SORT 与"列是否声明 NOT NULL"有关（加 NOT NULL 约束后 SORT 消失），与数据内容无关——两边的共性规律是：优化器按"结构/语义声明"决策，不按数据内容决策；差异在于 DM 的 NULL 物理位序（索引头）与排序语义（NULL 尾）冲突，才需要 SORT3 兜底。
3. IN 多值 + 大表（100 万行，命中 8 行跨 8 个分支）
CREATE TABLE ASC_TEST2 AS SELECT LEVEL ID,LEVEL*2 ID_CARD,LEVEL*10 PRO_NUM FROM DUAL CONNECT BY LEVEL < 1000000;
CREATE INDEX IDX_ASC2 ON ASC_TEST2(ID_CARD,PRO_NUM);
SELECT PRO_NUM FROM ASC_TEST2 WHERE ID_CARD IN (100,200,300,500,1000,2000,3000,5000) ORDER BY PRO_NUM ASC;
-- AUTOTRACE：8 rows processed / 1 sorts (memory) / 0 sorts (disk) / 79 consistent gets
-- 计划：INLIST ITERATOR → INDEX RANGE SCAN（IDX_ASC2）
💡 IN 每个分支内部有序，但跨分支合并后 PRO_NUM 整体无序，Oracle 在内存做了一次轻量合并排序（sorts=1、纯内存、无磁盘 IO），开销极小。
对照价值：连 Oracle 在"跨分支合并"时也会产生内存排序——说明排序本身不是问题，问题只在"单支索引扫描顺序与排序语义不一致"时引擎被迫挂完整 SORT 算子。DM 默认参数正属于后者。
4. 对照"需要排序"的场景（NULLS FIRST）——与 DM 对偶
EXPLAIN PLAN FOR
SELECT PRO_NUM FROM ASC_TEST1 WHERE ID_CARD = 200 ORDER BY PRO_NUM ASC NULLS FIRST;
Execution Plan -- 实测：SORT ORDER BY 出现了！
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (34) | 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 26 | 3 (34) | 00:00:01 | ← 新增排序
|* 2 | INDEX RANGE SCAN| IDX_ASC1 | 1 | 26 | 2 (0) | 00:00:01 |
----------------------------------------------------------------------------------
💡 意义：Oracle 默认 ASC = NULLS LAST（与索引物理序一致）免排序；一旦要求 ASC NULLS FIRST（与索引物理序相反），Oracle 同样被迫加 SORT ORDER BY。
这正是 DM
ORDER_BY_NULLS_FLAG=0场景的镜像：DM 索引物理序是 NULL 在前，FLAG=0 时 ASC 语义改为 NULL 在前（贴合物理序）→ 免排序；若保持 FLAG=1（要求 NULL 在后）→ 被迫 SORT3。两边机制完全对偶，只是默认值取向不同：Oracle 物理序=语义序（免排序）；DM 物理序≠兼容语义序（要排序）。
五、为什么 DM 用 ORDER_BY_NULLS_FLAG=0 就能规避？（机制详解）
一句话：ORDER_BY_NULLS_FLAG=0 把"升序时 NULL 排在哪"的语义，从"NULL 排最后（Oracle 兼容）“改成"NULL 排最前”，正好等于 DM 升序索引的物理顺序；语义与物理一致后，引擎就不需要再额外搬 NULL，SORT3 自然消失。
| 要素 | 含义 | DM 默认(FLAG=1) | DM 设 0 后 | Oracle |
|---|---|---|---|---|
| 索引物理序（建好即定死） | NULL 在 B+树升序索引里的位置 | NULL 在前 | NULL 在前 | NULL 在后 |
| 排序语义 (ORDER BY col ASC) | 结果集里 NULL 排哪 | NULL 在后（兼容 Oracle） | NULL 在前 | NULL 在后 |
| 物理序 == 语义序？ | — | ❌ 不一致 | ✅ 一致 | ✅ 一致 |
| 是否需 SORT3 | — | ✅ 需要（搬 NULL） | ❌ 不需要 | ❌ 不需要 |
为什么 FLAG=0 就"敢"不排序？ 因为语义改成"ASC 时 NULL 在最前"，与索引物理序完全一致，扫出来直接返回就是正确结果 → 优化器无后顾之忧，直接免掉 SORT3 算子，仅用 SSEK2 索引扫描输出。代价是结果集中 NULL 与 Oracle 相反（这也是 FLAG=1 才是默认值的原因——默认保 Oracle 语义，牺牲性能换兼容）。
四种规避手段对比：
| 手段 | 写法 | 生效范围 | 说明 |
|---|---|---|---|
| hint | /*+ ORDER_BY_NULLS_FLAG(0) */ |
语句级 | 最精准，只影响该 SQL。实测生效（即使 ENABLE_INJECT_HINT=0） |
| 会话参数 | SP_SET_PARA_VALUE(1,'ORDER_BY_NULLS_FLAG',0) |
当前会话 | 断开即还原，适合应用连接会话统一设置 |
| 系统参数 | SP_SET_PARA_VALUE(2,'ORDER_BY_NULLS_FLAG',0) |
全库（需重启） | 全局规避；但等于放弃"升序 NULL 最后"的 Oracle 兼容布局，需业务评估 NULL 排序预期 |
| 降序索引 | CREATE INDEX ... (col DESC) |
对象级 | 一头治：ASC 查询走 DESC 索引反向扫免 SORT |
⚠️ 注意 trade-off：
ORDER_BY_NULLS_FLAG=0相当于让"升序时 NULL 排最前"（按索引物理序），结果集与 Oracle 默认（NULL 最后）不一致。若应用依赖"升序查询 NULL 在末尾"，改全局参数前务必确认；只对个别 SQL 建议用 hint 或降序索引，避免全局语义变更。
六、结论
| 维度 | 结论 |
|---|---|
| 现象 | DM 升序索引 + ASC 排序出现多余 SORT3；Oracle 同 SQL 直接 INDEX RANGE SCAN 免排序——双侧已本机如实复现（Oracle 19.3 / DM8） |
| 根因 | NULL 在两边索引中的位序不同（Oracle 索引尾 / DM 索引头）+ DM 默认 ORDER_BY_NULLS_FLAG=1 要保 Oracle 语义 → 补排序。Oracle 索引物理序与排序语义天然一致，无需排序 |
| 关键验证 | ① Oracle 数据里插入真实 NULL 行（ID_CARD=200 有 2 行 NULL）后，INDEX RANGE SCAN 依旧、sorts=0——排序决策与"数据有没有 NULL"无关，取决于结构语义；② Oracle NULLS FIRST 时同样出现 SORT ORDER BY，与 DM FLAG=0 场景完全对偶 |
| 修复/规避 | ORDER_BY_NULLS_FLAG=0（hint / 会话 / 系统三级任选）或建降序索引，均实测消除 SORT。本质是让"排序语义"对齐"索引物理序" |
| 对迁移的启示 | MySQL/Oracle→DM 迁移后，若发现"同样 SQL 多出 SORT 算子"，先查 ORDER_BY_NULLS_FLAG 与 NULL 排序语义（以及列是否声明 NOT NULL），这是常见迁移性能差异点 |
七、总结
本文通过 DM8 与 Oracle 19.3 双侧对照实验，把"升序索引多一个 SORT"这个看似怪异的现象拆解得明明白白：
现象：DM 升序索引 + ORDER BY ASC → SORT3；降序索引 → 无 SORT；Oracle 同 SQL → 无 SORT
↓
根因：NULL 在 B+树升序索引中的物理位序不同
↓
Oracle：NULL 在索引尾 = ASC 默序（NULL 在后）→ 免排序
DM（默认 FLAG=1）：NULL 在索引头 ≠ ASC 兼容语义（NULL 在后）→ 必须 SORT3 搬 NULL
↓
规避：ORDER_BY_NULLS_FLAG=0（hint/会话/系统）或降序索引
= 让"排序语义"对齐"索引物理序"，SORT 自然消失
核心要点可归纳为三句话：
- 机制上：排序决策取决于"索引物理序 vs 排序语义"是否一致，与数据里有没有 NULL 无关（结构语义决策，非数据内容决策）；
- 规避上：
ORDER_BY_NULLS_FLAG=0三档任选（hint 最精准、会话次之、系统全局需评估），或直接建降序索引反向扫； - 迁移上：Oracle/MySQL→DM 迁移后同 SQL 多出 SORT 算子，第一反应查
ORDER_BY_NULLS_FLAG与 NULL 排序语义、列是否 NOT NULL，这是常见且易被忽略的性能差异点。
作者
文章
阅读量
获赞
联系客服
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1