1.背景

近期在某个医院的his系统项目的适配过程中，发现在rownum=1的场景下，实际执行结果不符合预期。业务查询场景发生在一个很普通的视图自关联查询。而此业务场景下有意思的是，如果正常去执行这个自关联查询语句，执行时间是 0.036秒左右，但是在sql语句后面加一个 rownum=1 的条件后，执行时间就需要 9秒左右。应用可能会由于等待超时报错。引发系统故障。

sql语句如下：

select t.report_status_code, t.item_code
from zoeapply.V_APP_INP_APPLY t, zoeapply.V_APP_INP_APPLY d
where nvl(t.Append_Bill_Flag, 0) = '0'
	and d.pres_no = t.pres_no
	and d.pres_sub_no = t.pres_sub_no
	and d.apply_no = 18625695
	and rownum = 1;

2.问题排查过程

2.1.执行计划对比

首先，对比下二个sql语句的执行计划。

不带 rownum 条件的执行计划：

带rownum 条件的执行计划：

2.2.执行计划分析

整体看这2个执行计划，他们都是走的嵌套连接（ NEST LOOP INNER JOIN2 ）。

这里有2个地方是不一样的：

不带rownum条件：驱动表是 d，使用 SSEK2 索引来过滤驱动表的数据。
带rownum条件：驱动表是 t，使用 CSCN2 聚集索引扫描来过滤驱动表的数据。

2.3.驱动表路径分析

此时需要详细分析下为什么出现了上述两处不同。

先分析下，不带rownum条件，驱动表是d表的情况。d 表的过滤条件是 d.apply_no = 18625695 ，可以直接使用二级索引定位，快速做回表查询。数据量还比较少很适合做驱动表。

再分析，带rownum条件：驱动表是t。t表的过滤条件是 nvl(t.Append_Bill_Flag, 0) = ‘0’，它只能去全表搜索满足条件的行返回，不能使用聚簇键直接定位。这应该是慢的原因之一。

那为什么加了rownum =1 的条件后，优化器选择驱动表调整了。因为 rownum的条件并没有作用到驱动表上，并不是驱动表直接返回1条满足条件的数据，驱动表还是需要把满足条件的数据都找到，然后做嵌套连接后再做 rownum = 1 的返回。

这里疑惑的点就是，驱动表并不能因为rownum = 1的条件返回1条数据，那它为什么会判断使用 t 表的过滤条件是 nvl(t.Append_Bill_Flag, 0) = ‘0’ 这个过滤条件会更好。

实际的情况是使用 d.apply_no = 18625695的过滤条件做驱动表更好。

2.4.trace计划分析

这里就需要使用 10053 trace，文件来分析下，优化器选择驱动表的根据。

10053中我类比的是 rownum <= 200 和 rownum = 1 这2个sql语句

由于sql运行环境所限，生成10053事件使用的条件是rownum <= 200 和 rownum = 1 这2个sql语句。

rownum<200模拟的是不带rownum=1条件的情况。

在这个例子中，rownum <=200 执行计划是快的， rownum = 1 这个语句的执行计划是慢的。从 10053 中可以看到，优化器对表的描述信息是一致的，并且表都没有统计信息。优化器也没有利用统计信息。

2.4.1.rownun<200的trace分析

优化器在判断 rownum <=200 的sql时，显示用t表做驱动表，过滤条件是 nvl(t.Append_Bill_Flag, 0) = ‘0’ 发现 nest loop inner join cost: 534.23719，hash inner join cost: 434.88409。它对比了这2种连接方式，只是给出了 hash join 的执行计划

但是，由于2种连接方式的cost都比较大。之后优化器再用 d表做驱动表过滤条件是 d.apply_no = 18625695。这回nest loop inner join cost: 45.85489 嵌套连接的成本最小。选对了执行计划。

2.4.2.rownum=1的trace分析

优化器在判断 rownum= 1的sql时，同样也是先用 t 表做驱动表，过滤条件是 nvl(t.Append_Bill_Flag, 0) = ‘0’ 驱动表预估的返回值是 47，但是它的嵌套连接的cost 是 11. 这样优化器认为这个执行计划就是最优的，没有再尝试用d表做驱动表。从而也没有办法对比d表做驱动表的情况。这样也就选择了一个优化器认为比较好，但实际很差的一个执行计划。

3.排查结果

为什么优化器对表的分析是一样的，都是返回47条数据。只是 rownum 的条件不一样，cost的计算差那么大。

有可能是这个原因：

NLJ cost = consumed_rows × (outer_total_cost / outer_card) ← 外表摊销
+ consumed_rows × inner_per_probe_cost ← 内表探测
其中 consumed_rows = min(rownum, outer_card)。

这个公式本质上在"假装"外表是逐行产出的——每读1 行花 outer_total/card 的代价，读 consumed_rows 行就花 consumed_rows × (outer_total/card)。

两条 SQL 套的是同一个公式，差别只在 consumed_rows 这一项。

第一条SQL（rownum<=200）的 NLJ cost ≈ 530，HJ cost ≈ 434, HJ< NLJ，HJ 胜出。但是当变化驱动表后， NLJ cost ≈ 45 。所以选对了执行计划。
第二条 SQL（rownum=1）的 NLJ cost ≈ 11.17，HJ cost 还是 ≈ 434，NLJ < HJ → NLJ 胜出。并且它没有去尝试变化驱动表。

那这个问题主要就是出现在 distinct上，正常的sql NLJ 从外表拿到第 1 行 → 立刻去探测内表 → 命中 → rownum=1 满足 → 整个查询终止，外表后面的行根本不会被读（备注：DISTINCT、UNION ALL、SORT、物化这一类叫阻塞算子（blocking operator）——必须把输入全部读完、去重完，才能产出第 1 行）。

4. 处理方法

1.这个环境的数据库是2024年第一季度的版本，可以升级到最新的版本来规避这个问题。
2.可以临时添加 hint 来规避这个问题，驱动表可以选择过滤条件返回较少数据量的表。比如，指定驱动表 /*+ use_nl(dd tt) */。这个测试结果是正确的，是可以正常使用的。

关于rownum = 1的优化分析