• 问题描述
• 问题分析
• 解决方法
• 问题总结

最近我们在处理一个基于 OceanBase 3.1.2 bp8 版本（2-2-2 部署模式）Oracle 租户的业务查询时，就遇到了这样一个典型案例：一条包含左外连接且关联条件带 OR 的 SQL，执行效率异常低下。当我们分别测试 OR 连接的两个条件时，每个单独的关联条件都能快速返回结果。正是这个 OR 的存在，让查询性能出现了巨大落差。

问题描述

3.1.2 bp8 2-2-2部署模式

oracle租户

SQL select * from ( select a.*, ROWNUM rnum from ( 

select distinct RES.*,

VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,
VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,
VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_,
VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_
from temp RES
left outer join ACT_HI_VARINST VAR ON RES.ID_ = VAR.TASK_ID_ or (RES.PROC_INST_ID_ = VAR.EXECUTION_ID_)
WHERE RES.END_TIME_ is null 
order by RES.START_TIME_ desc, VAR.LAST_UPDATED_TIME_ asc
) a where ROWNUM = 1

问题分析

深入分析后，发现问题的核心在于 SQL 语句的写法不够规范：

1. left outer join ACT_HI_VARINST VAR ON RES.ID_ = VAR.TASK_ID_ or (RES.PROC_INST_ID_ = VAR.EXECUTION_ID_) 这种写法是不规范，尝试去掉or (RES.PROC_INST_ID_ = VAR.EXECUTION_ID_) 查询立即出结果，去掉 RES.ID_ = VAR.TASK_ID_ or 也立即出结果。

2. 针对这一问题，我们采用了等价改写策略：left outer join ACT_HI_VARINST VAR ON RES.ID_ = VAR.TASK_ID_ or (RES.PROC_INST_ID_ = VAR.EXECUTION_ID_) 写成两次关联，再通过union 来合并去重。

3. 最后对UNOIN合并后的完整结果集，按 START_TIME_ 降序、 LAST_UPDATED_TIME_ 升序进行排序，既保证了逻辑等价性，又大幅提升了执行效率。

解决方法

优化后执行2S左右

SELECT * FROM (
SELECT * FROM (
SELECT a.*, rownum rnum FROM (
select RESS.ID_,RESS.REV_,RESS.PROC_DEF_ID_,RESS.TASK_DEF_ID_,RESS.TASK_DEF_KEY_,RESS.PROC_INST_ID_,RESS.EXECUTION_ID_,RESS.SCOPE_ID_,RESS.SUB_SCOPE_ID_,RESS.SCOPE_TYPE_,RESS.SCOPE_DEFINITION_ID_,RESS.NAME_,RESS.PARENT_TASK_ID_,RESS.DESCRIPTION_,RESS.OWNER_,RESS.ASSIGNEE_,RESS.START_TIME_,RESS.CLAIM_TIME_,RESS.END_TIME_,RESS.DURATION_,RESS.DELETE_REASON_,RESS.PRIORITY_,RESS.DUE_DATE_,RESS.FORM_KEY_,RESS.CATEGORY_,RESS.TENANT_ID_,RESS.LAST_UPDATED_TIME_,
VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,
VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,
VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_,
VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_
from  temp RES
left outer join ACT_HI_VARINST VAR ON RESS.ID_ = VAR.TASK_ID_ 
WHERE RESS.END_TIME_ is null 
UNION 
select RES.ID_,RES.REV_,RES.PROC_DEF_ID_,RES.TASK_DEF_ID_,RES.TASK_DEF_KEY_,RES.PROC_INST_ID_,RES.EXECUTION_ID_,RES.SCOPE_ID_,RES.SUB_SCOPE_ID_,RES.SCOPE_TYPE_,RES.SCOPE_DEFINITION_ID_,RES.NAME_,RES.PARENT_TASK_ID_,RES.DESCRIPTION_,RES.OWNER_,RES.ASSIGNEE_,RES.START_TIME_,RES.CLAIM_TIME_,RES.END_TIME_,RES.DURATION_,RES.DELETE_REASON_,RES.PRIORITY_,RES.DUE_DATE_,RES.FORM_KEY_,RES.CATEGORY_,RES.TENANT_ID_,RES.LAST_UPDATED_TIME_,VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_,
VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, VAR.LONG_ as VAR_LONG_
from ACT_HI_TASKINST RES 
left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_
WHERE RES.END_TIME_ is null 
) a ORDER BY a.START_TIME_ desc, a.LAST_UPDATED_TIME_ asc
) B WHERE ROWNUM=1

问题总结

1. 该问题根源在于 OceanBase 3.1.2 版本优化器处理不好 left outer join 连接条件带or的情况，而此问题在OceanBase 3.2 版本中已得到修复；

2. 原 SQL 写法不规范：使用select distinct RES.*进行模糊查询，未显式列出所需字段；

3. 对比测试显示，Oracle 优化器对 left outer join 连接条件带or的场景处理能力更优，相同 SQL 在 Oracle 环境下可实现 “秒出结果”。

后续我们还会持续分享数据库实战优化技巧，无论是 OceanBase 还是 Oracle 的性能调优案例，都将第一时间呈现。如果你的业务中也遇到过类似难题，欢迎在评论区留言讨论，下次见！