我们的文章会在微信公众号 IT民工的龙马人生 和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
1. 案例摘要
慢因： 多层 OR 套 IN 在崖山落成外层驱动的多路相关 SUBQUERY，谓词为 EXISTS QUERY[…] OR …，内层随外层行反复探测（Loops 上千），逻辑读与耗时被放大。同 SQL 在 Oracle 侧已是 VW_ORE_* + UNION-ALL，FILTER 可见 LNNVL(EXISTS …)，两边路径不同。
改法： 将 OR 拆成多臂 UNION，每臂用 EXISTS/JOIN 从工号正向推到订单；关联索引按已建好处理。
效果（YashanDB 23.5.2.101）： 结果均为 30 行。Elapsed 0.075s → 0.011s（0.075 / 0.011 ≈ 6.8，约 7 倍），db block gets 32762 → 3635（约 9 倍）。另测两路 OR：崖山从两路起即为双 SUBQUERY，并非三路才暴露。
2. 问题引入
生产引入
最近在某客户现场做 SQL 优化，遇到如下的一个问题。
SQL 在订单表上叠多层 IN，再用 OR 拼「当前工号相关」的单据。崖山上（关联索引已在）跑下来约 0.075s，计划里一串 SUBQUERY，外层谓词为 EXISTS QUERY[5] OR EXISTS QUERY[3] OR EXISTS QUERY[2]，内层 Loops 到几千，db block gets 约 32762。同 SQL 在 Oracle 上拉计划作对照：已是 VW_ORE_* + UNION-ALL，FILTER 分支上有 LNNVL(EXISTS …)，与崖山相关子查询路径对不上。
原始 SQL
以下 SQL 在模拟环境中复现原生产侧同类写法（脱敏/仿建对象 wh_*，非生产库原文直贴）：
SELECT DISTINCT sku_code AS skuCode,
sku_name AS skuName
FROM wh_order
WHERE ord_id IN (
SELECT ord_id
FROM wh_touch_log
WHERE staff_id IN (
SELECT staff_id
FROM wh_staff
WHERE badge_no = 'B9001'
)
)
OR opener_id IN (
SELECT staff_id
FROM wh_staff
WHERE badge_no = 'B9001'
)
OR ord_id IN (
SELECT ord_id
FROM wh_watcher
WHERE staff_id IN (
SELECT staff_id
FROM wh_staff
WHERE badge_no = 'B9001'
)
);
| 结构 | 作用 |
|---|---|
DISTINCT sku_code, sku_name |
按 SKU 去重 |
ord_id IN (… wh_touch_log …) |
触摸日志里出现过的订单 |
opener_id IN (… wh_staff …) |
开单人是该工号 |
ord_id IN (… wh_watcher …) |
观察者里有该工号 |
三路 OR |
命中一路即可 |
3. 分析问题
分析思路：
- 如何确认是慢 SQL： 崖山
STATISTICS_LEVEL=ALL+AUTOTRACE TRACEONLY，Elapsed0.075s，db block gets=32762，结果仅 30 行，代价偏高。 - 如何定位到慢的地方： 三路
SUBQUERY（QUERY[2/3/5]）挂在WH_ORDER全表外；Operation Information 中 Id 17 为EXISTS QUERY[5] OR EXISTS QUERY[3] OR EXISTS QUERY[2]。外层一行探三路，索引只能降低单次探测成本，挡不住 Loops 放大。
优化思路：
Oracle 同 SQL 对照为 VW_ORE_* + UNION-ALL，FILTER 上 EXISTS … OR … AND LNNVL(EXISTS …)，即析取展开。崖山参数 FILTER_OR_TO_UNION 默认 TRUE（文档口径：子查询动态改写为集合操作），对本案多层相关 IN+OR 实测仍落在相关 SUBQUERY。因此应用侧手写拆成 UNION，从工号侧正向驱动；语义用 UNION 去重对齐原 DISTINCT。不必照抄 Oracle 10053 内部的 UNION ALL+LNNVL 形态。
根因一句话：慢在外层驱动的多路相关 SUBQUERY，不在「缺索引」本身。
4. 环境与数据
在模拟环境中按生产侧同类问题仿建对象并灌数（非生产库直接操作）。Oracle / 崖山同结构、同规模；对比时关联索引均已建好。
| 项 | 内容 |
|---|---|
| Oracle（对照） | Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production |
| 崖山 | YashanDB Server Enterprise Edition Release 23.5.2.101 aarch64 |
| 表规模 | wh_staff 200；wh_order 2000；wh_touch_log 5000；wh_watcher 4000 |
| 索引 | PK；idx_wh_staff_badge、idx_wh_touch_staff、idx_wh_touch_ord、idx_wh_watch_staff、idx_wh_watch_ord、idx_wh_order_opener、idx_wh_order_sku |
| 统计信息 | 已收集 |
| 取证 | STATISTICS_LEVEL=ALL + AUTOTRACE TRACEONLY + TIMING ON |
| 参数 | FILTER_OR_TO_UNION=TRUE |
| 计时说明 | 正文 Elapsed / Statistics 为模拟环境当轮落盘值；按博客规范正式发布前，建议同一 SQL 连续执行 3 次、取第 3 次复核 |
5. 解决方案
5.1 优化前（OR + 多层 IN）
SQL 见第 2 章。先看崖山，再贴 Oracle 同 SQL 计划作对照。
崖山计划
+----+--------------------------------+----------------------+----------+----------+----------+
| Id | Operation type | Name | A-Rows | A-Time | Loops |
+----+--------------------------------+----------------------+----------+----------+----------+
| 0 | SELECT STATEMENT | | 30| 48963| 31|
| 1 | SUBQUERY | QUERY[5] | 414| 24659| 2000|
| 2 | NESTED LOOPS SEMI | | 414| 24520| 2000|
| 3 | TABLE ACCESS BY INDEX ROWID | WH_WATCHER | | | |
|* 4 | INDEX RANGE SCAN | IDX_WH_WATCH_ORD | 3743| 2403| 5329|
|* 5 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | | | |
|* 6 | INDEX UNIQUE SCAN | SYS_C_57346 | 414| 1841| 3743|
| 7 | SUBQUERY | QUERY[3] | 30| 1039| 1586|
|* 8 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | | | |
|* 9 | INDEX UNIQUE SCAN | SYS_C_57346 | 30| 938| 1586|
| 10 | SUBQUERY | QUERY[2] | 100| 22275| 1556|
| 11 | NESTED LOOPS SEMI | | 100| 22132| 1556|
| 12 | TABLE ACCESS BY INDEX ROWID | WH_TOUCH_LOG | | | |
|*13 | INDEX RANGE SCAN | IDX_WH_TOUCH_ORD | 3740| 1968| 5196|
|*14 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | | | |
|*15 | INDEX UNIQUE SCAN | SYS_C_57346 | 100| 1628| 3740|
| 16 | SORT DISTINCT | | 30| 48953| 31|
|*17 | TABLE ACCESS FULL | WH_ORDER | 544| 48818| 545|
+----+--------------------------------+----------------------+----------+----------+----------+
谓词（Operation Information）：
4 - Predicate : access("WH_WATCHER"."ORD_ID" = "WH_ORDER"."ORD_ID")
5 - Predicate : filter("WH_STAFF"."BADGE_NO" = 'B9001')
6 - Predicate : access("WH_STAFF"."STAFF_ID" = "WH_WATCHER"."STAFF_ID")
8 - Predicate : filter("WH_STAFF"."BADGE_NO" = 'B9001')
9 - Predicate : access("WH_STAFF"."STAFF_ID" = "WH_ORDER"."OPENER_ID")
13 - Predicate : access("WH_TOUCH_LOG"."ORD_ID" = "WH_ORDER"."ORD_ID")
14 - Predicate : filter("WH_STAFF"."BADGE_NO" = 'B9001')
15 - Predicate : access("WH_STAFF"."STAFF_ID" = "WH_TOUCH_LOG"."STAFF_ID")
16 - Distinct Expression: ("WH_ORDER"."SKU_CODE", "WH_ORDER"."SKU_NAME")
17 - Predicate : filter(EXISTS QUERY[5] OR EXISTS QUERY[3] OR EXISTS QUERY[2])
| 项 | 值 |
|---|---|
| Elapsed | 00:00:00.075 |
| db block gets | 32762 |
| rows processed | 30 |
| physical reads | 0 |
Oracle 同 SQL（对照）
Plan hash value: 3374631224
| Id | Operation | Name | Rows | Cost |
| 0 | SELECT STATEMENT | | 272 | 239 |
| 1 | HASH UNIQUE | | 272 | 239 |
| 2 | VIEW | VW_ORE_65D8B21A | 272 | 238 |
| 3 | UNION-ALL | | | |
|* 4 | HASH JOIN RIGHT SEMI | | 262 | 11 |
| 5 | VIEW | VW_NSO_1 | 262 | 7 |
|* 6 | HASH JOIN | | 262 | 7 |
| 7 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | 10 | 2 |
|* 8 | INDEX RANGE SCAN | IDX_WH_STAFF_BADGE | 10 | 1 |
| 9 | TABLE ACCESS FULL | WH_TOUCH_LOG | 5000 | 5 |
| 10 | TABLE ACCESS FULL | WH_ORDER | 2000 | 4 |
|* 11 | FILTER | | | |
| 12 | TABLE ACCESS FULL | WH_ORDER | 2000 | 4 |
|* 13 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | 1 | 1 |
|* 14 | INDEX UNIQUE SCAN | SYS_C007485 | 1 | 0 |
| 15 | NESTED LOOPS SEMI | | 2 | 4 |
| 16 | TABLE ACCESS BY INDEX ROWID | WH_WATCHER | 2 | 3 |
|* 17 | INDEX RANGE SCAN | IDX_WH_WATCH_ORD | 2 | 1 |
|* 18 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | 5 | 1 |
|* 19 | INDEX UNIQUE SCAN | SYS_C007485 | 1 | 0 |
|* 20 | HASH JOIN SEMI | | 2 | 6 |
| 21 | TABLE ACCESS BY INDEX ROWID | WH_TOUCH_LOG | 3 | 4 |
|* 22 | INDEX RANGE SCAN | IDX_WH_TOUCH_ORD | 3 | 1 |
| 23 | TABLE ACCESS BY INDEX ROWID | WH_STAFF | 8 | 2 |
|* 24 | INDEX RANGE SCAN | IDX_WH_STAFF_BADGE | 10 | 1 |
谓词（Predicate Information）：
4 - access("ORD_ID"="ORD_ID")
6 - access("STAFF_ID"="STAFF_ID")
8 - access("BADGE_NO"='B9001')
11 - filter(( EXISTS (SELECT 0 FROM "WH_STAFF" "WH_STAFF"
WHERE "STAFF_ID"=:B1 AND "BADGE_NO"='B9001')
OR EXISTS (SELECT 0 FROM "WH_WATCHER" "WH_WATCHER","WH_STAFF" "WH_STAFF"
WHERE "STAFF_ID"="STAFF_ID" AND "BADGE_NO"='B9001' AND "ORD_ID"=:B2))
AND LNNVL( EXISTS (SELECT 0 FROM "WH_TOUCH_LOG" "WH_TOUCH_LOG","WH_STAFF" "WH_STAFF"
WHERE "BADGE_NO"='B9001' AND "STAFF_ID"="STAFF_ID" AND "ORD_ID"=:B3)))
13 - filter("BADGE_NO"='B9001')
14 - access("STAFF_ID"=:B1)
17 - access("ORD_ID"=:B1)
18 - filter("BADGE_NO"='B9001')
19 - access("STAFF_ID"="STAFF_ID")
20 - access("STAFF_ID"="STAFF_ID")
22 - access("ORD_ID"=:B1)
24 - access("BADGE_NO"='B9001')
Id 11 上的 LNNVL(EXISTS …) 是展开后分支去重用的。这次对照：Elapsed 0.03s，consistent gets 12673，30 行。
一句话对照：Oracle 已是 VW_ORE + UNION-ALL；崖山仍是三路相关 SUBQUERY，外层 EXISTS QUERY[n] OR …。
5.2 改写参考：Oracle 10053 改写后的 SQL
同 SQL 在 Oracle 上开 10053，trace 里 ORE: after OR Expansion 一段 UNPARSED QUERY 如下（整理可读）。优化器拆成 VW_ORE_* + UNION ALL，第二臂用 LNNVL 去掉第一臂已命中的行，可与上面计划里 FILTER 的 LNNVL 对照。放在崖山改写前，仅作改写参考。
SELECT DISTINCT
vw_ore.item_1 AS skuCode,
vw_ore.item_2 AS skuName
FROM (
-- 第 1 臂：touch_log 一路
SELECT wh_order.sku_code AS item_1,
wh_order.sku_name AS item_2
FROM wh_order
WHERE wh_order.ord_id = ANY (
SELECT wh_touch_log.ord_id
FROM wh_touch_log
WHERE wh_touch_log.staff_id = ANY (
SELECT wh_staff.staff_id
FROM wh_staff
WHERE wh_staff.badge_no = 'B9001'
)
)
UNION ALL
-- 第 2 臂：opener 或 watcher，并用 LNNVL 排除已在第 1 臂命中的行
SELECT wh_order.sku_code AS item_1,
wh_order.sku_name AS item_2
FROM wh_order
WHERE (
wh_order.opener_id = ANY (
SELECT wh_staff.staff_id
FROM wh_staff
WHERE wh_staff.badge_no = 'B9001'
)
OR wh_order.ord_id = ANY (
SELECT wh_watcher.ord_id
FROM wh_watcher
WHERE wh_watcher.staff_id = ANY (
SELECT wh_staff.staff_id
FROM wh_staff
WHERE wh_staff.badge_no = 'B9001'
)
)
)
AND LNNVL(
wh_order.ord_id = ANY (
SELECT wh_touch_log.ord_id
FROM wh_touch_log
WHERE wh_touch_log.staff_id = ANY (
SELECT wh_staff.staff_id
FROM wh_staff
WHERE wh_staff.badge_no = 'B9001'
)
)
)
) vw_ore;
应用侧不必照抄 UNION ALL + LNNVL；崖山落地见下一节更直白的三臂 UNION + EXISTS。
5.3 崖山改写：UNION 拆 OR
三路 OR 改成三臂 UNION，每臂从 badge_no 往里推：
SELECT sku_code AS skuCode,
sku_name AS skuName
FROM (
SELECT sku_code, sku_name
FROM wh_order o
WHERE EXISTS (
SELECT 1
FROM wh_touch_log t
JOIN wh_staff s ON s.staff_id = t.staff_id
WHERE t.ord_id = o.ord_id
AND s.badge_no = 'B9001'
)
UNION
SELECT sku_code, sku_name
FROM wh_order o
WHERE EXISTS (
SELECT 1
FROM wh_staff s
WHERE s.staff_id = o.opener_id
AND s.badge_no = 'B9001'
)
UNION
SELECT sku_code, sku_name
FROM wh_order o
WHERE EXISTS (
SELECT 1
FROM wh_watcher w
JOIN wh_staff s ON s.staff_id = w.staff_id
WHERE w.ord_id = o.ord_id
AND s.badge_no = 'B9001'
)
);
崖山计划：
+----+--------------------------------+----------------------+----------+----------+----------+
| Id | Operation type | Name | A-Rows | A-Time | Loops |
+----+--------------------------------+----------------------+----------+----------+----------+
| 0 | SELECT STATEMENT | | 30| 1667| 31|
| 1 | VIEW | | 30| 1655| 31|
| 2 | SORT DISTINCT | | 30| 1653| 31|
| 3 | UNION ALL | | 894| 1507| 895|
| 4 | NESTED LOOPS INNER | | 290| 650| 291|
| 7 | NESTED LOOPS INNER | | 725| 212| 726|
|* 9 | INDEX RANGE SCAN | IDX_WH_STAFF_BADGE | 10| 8| 11|
|*11 | INDEX RANGE SCAN | IDX_WH_TOUCH_STAFF | 725| 101| 735|
|*13 | INDEX UNIQUE SCAN | SYS_C_57348 | 290| 178| 580|
| 14 | NESTED LOOPS INNER | | 190| 79| 191|
|*17 | INDEX RANGE SCAN | IDX_WH_STAFF_BADGE | 10| 3| 11|
|*19 | INDEX RANGE SCAN | IDX_WH_ORDER_OPENER | 190| 25| 200|
| 20 | NESTED LOOPS INNER | | 414| 698| 415|
|*25 | INDEX RANGE SCAN | IDX_WH_STAFF_BADGE | 10| 3| 11|
|*27 | INDEX RANGE SCAN | IDX_WH_WATCH_STAFF | 514| 91| 524|
|*29 | INDEX UNIQUE SCAN | SYS_C_57348 | 414| 279| 828|
+----+--------------------------------+----------------------+----------+----------+----------+
谓词：
2 - Distinct Expression: ("VSQ$0@SEL$7"."SKU_CODE", "VSQ$0@SEL$7"."SKU_NAME")
9 - Predicate : access("S"."BADGE_NO" = 'B9001')
11 - Predicate : access("T"."STAFF_ID" = "S"."STAFF_ID")
13 - Predicate : access("O"."ORD_ID" = "VSQ$1@SEL$1"."ORD_ID")
17 - Predicate : access("S"."BADGE_NO" = 'B9001')
19 - Predicate : access("O"."OPENER_ID" = "S"."STAFF_ID")
25 - Predicate : access("S"."BADGE_NO" = 'B9001')
27 - Predicate : access("W"."STAFF_ID" = "S"."STAFF_ID")
29 - Predicate : access("O"."ORD_ID" = "VSQ$1@SEL$5"."ORD_ID")
| 项 | 值 |
|---|---|
| Elapsed | 00:00:00.011 |
| db block gets | 3635 |
| rows processed | 30 |
行数与改写前一致，都是 30。
5.4 两路 OR 顺手看一眼
把第三臂（wh_watcher）去掉。崖山还是双路 SUBQUERY，外层谓词变成 EXISTS QUERY[3] OR EXISTS QUERY[2]：Elapsed 0.044s，db block gets 20771，14 行。改成两臂 UNION 后：0.009s / 1848，仍是 14 行。
Oracle 同 SQL（两路）对照：计划仍是 VW_ORE_* + UNION-ALL，FILTER 上有 LNNVL(EXISTS …)；Elapsed 0.01s，consistent gets 1148，14 行。
所以：崖山从两路 OR 起就会掉进相关 SUBQUERY；不是非要三路才暴露。
6. 对比总表与提升倍数
| 指标 | 改写前 | UNION 后 |
|---|---|---|
| 行数 | 30 | 30 |
| Elapsed | 0.075s | 0.011s |
| 关键算子 | 三路 SUBQUERY |
UNION ALL + NL |
| 外层谓词 | EXISTS QUERY[5] OR … |
各臂 access(badge_no/…) |
| db block gets | 32762 | 3635 |
| 顶层 A-Time（µs） | 48963 | 1667 |
耗时：提升倍数 = 0.075 / 0.011 ≈ 6.8（约 7 倍）。逻辑读：32762 / 3635 ≈ 9.0。
和 Oracle 同 SQL对照：
| Oracle | 崖山（改写前） | |
|---|---|---|
| 计划 | VW_ORE_* + UNION-ALL |
多路相关 SUBQUERY |
| 关键谓词 | LNNVL(EXISTS …) |
EXISTS QUERY[n] OR … |
| 两路 OR | 已展开 | 已是双 SUBQUERY |
7. 建议及总结
碰到「多路 OR + 多层 IN」这类权限/参与过滤，在崖山上优先拆 UNION，别指望一定出现和 Oracle 一样的自动 OR Expansion。关联索引该有还是要有，但单靠索引消不掉相关 SUBQUERY 主路径。各臂要能独立写清楚、并集语义要正确；有选择性好的驱动列（如工号）时收益更稳。UNION 还是 UNION ALL 按去重要求选。现场数据量不同，倍数以现场为准。
可推广条件：WHERE 顶层 ≥2 路 OR，臂内相关 IN/EXISTS，计划出现多路 SUBQUERY + 高 Loops。风险：臂语义写错会改变结果集；用 UNION ALL 时须自行处理去重。本案例未在生产直连复测；正式发博客前建议按「同一 SQL 连续 3 次、取第 3 次」复核耗时。
参数备忘：FILTER_OR_TO_UNION 默认 TRUE，是否开启子查询动态改写为集合操作；对本形态仍可能不展开。
SQL优化多层OR相关子查询改UNION性能提升7倍：等您坐沙发呢！