往期回顾：

在Oracle数据库开发中，复杂子查询往往隐藏着性能陷阱。本文作为《左右SQL执行计划妙招》系列第七篇，将通过一个真实案例展示ROWNUM实体化技术如何解决笛卡尔积问题。

问题案例

某运营商系统中的问题SQL：

select ta.tch_id, ta.flow_id                                      
from tache pr,                                         
     (select TCH_ID, c.flow_id                         
        from event_q a, staff_event b, tache c         
       where (a.event_type = '2' OR a.event_type = '1')
         and a.event_id = b.event_id                   
         and (a.flag is null or a.flag = '1')          
         and b.staff_id = 1                            
         and a.content_id = c.tch_id) ta               
where ta.flow_id = pr.sub_flow_id(+)                    
  and pr.flow_id is null

这条SQL本身很复杂，我们把其他部分略去，只显示典型的子查询这一小部分，另外主要也是这一小部分慢。如果把这一小部分注释掉，执行可在1秒内完成，否则需要30秒才可以完成。

问题根源：笛卡尔积的产生

通过分析执行计划，可以清楚判断该子查询的连接顺序：首先是外面的TACHE表先和ta结果集的TACHE表连接，然后再和STAFF_EVENT连接，最后和EVENT_Q完成连接！

由于staff_event这个表和TACHE表没有连接条件，就是没有体现staff_event的某某字段和TACHE表的某某字段关联的地方，所以这里产生了笛卡尔积！因此本次查询非常慢。

解决思路分析

通过简单研究发现，只要ta结果集内部先完成表连接，再和外部连接，就不至于产生笛卡尔积了。因为在ta结果集里，我们可以让STAFF_EVENT b和EVENT_Q a先完成连接，它们是有连接条件的，条件是a.event_id = b.event_id。接下来这两个连接再和ta结果集中的TACHE c表连接，又有连接条件，条件是：a.content_id=c.tch_id。最后再和外面的tache pr表完成连接，连接条件是：ta.flow_id = pr.sub_flow_id(+)。如此看来，不可能有笛卡尔积！

而如何让Oracle的子查询不要先自行出去和外面表连接，再回过来连接内部剩下的部分呢？

解决方案：ROWNUM实体化

很简单，只要修改为如下语句即可，增加rownum部分：

select ta.tch_id, ta.flow_id                                      
from tache pr,                                         
     (select TCH_ID, c.flow_id,  rownum                          
        from event_q a, staff_event b, tache c         
       where (a.event_type = '2' OR a.event_type = '1')
         and a.event_id = b.event_id                   
         and (a.flag is null or a.flag = '1')          
         and b.staff_id = 1                            
         and a.content_id = c.tch_id) ta               
where ta.flow_id = pr.sub_flow_id(+)                    
  and pr.flow_id is null

为了保证rownum的结果集是对的，oracle不可能先出去和结果集外的表关联一部分再回到结果集内，只可能内部先关联了，因此，我们成功了！最终执行计划如下，笛卡尔积消失，总的SQL语句执行时间从30s变为1s。

性能对比

整个语句最终只返回42条记录，说明ta这个结果集本身返回的记录数并不多，虽然tache是一张大表，但是这段语句如果没有笛卡尔积，应该非常快！

在这里插入图片描述

替代方案

除了ROWNUM实体化，还可以使用WITH子句来强制子查询先执行：

with ta as (
  select TCH_ID, c.flow_id
  from event_q a, staff_event b, tache c
  where (a.event_type = '2' OR a.event_type = '1')
    and a.event_id = b.event_id
    and (a.flag is null or a.flag = '1')
    and b.staff_id = 1
    and a.content_id = c.tch_id
)
select ta.tch_id, ta.flow_id
from ta, tache pr
where ta.flow_id = pr.sub_flow_id(+)
  and pr.flow_id is null;

使用注意事项

适用场景：子查询结果集相对较小的复杂关联查询
内存开销：实体化需要额外内存存储中间结果
结果验证：确保添加ROWNUM不影响最终查询结果

总结

ROWNUM实体化是一个实用的SQL优化技巧，通过添加rownum强制Oracle优化器先完成子查询内部的连接，避免与外层表产生笛卡尔积。本案例仅仅添加一个rownum字段就实现了30倍的性能提升，是处理复杂子查询性能问题的有效手段。

未完待续..

左右SQL执行计划妙招 ⑧ ——执行计划SQL写法差异改变【rowid 的影响】

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑦——执行计划SQL写法差异改变【rownum实体化】