基于湖库一体架构，统一管理结构化、半结构化与非结构化等多模态数据，一个系统承载事务处理、实时分析与 AI 工作负载。
划线反馈
SQL 包含多个 union all 执行耗时慢的原因和解决方法
更新时间：2026-05-14 07:41
分享
适用版本：V1.4.x、V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x内容类型：Troubleshoot
问题现象
完整 SQL 语句是一个 union all 联合语句，当单独执行 union all 的上半段和下半段语句的执行效率都在 10 毫秒左右。当 union all 语句完整执行时，整体耗时约 500 毫秒。
union 的上半段和下半段的执行语句完全一致，本质只是条件值不一致，而且通过两个单独的逻辑执行计划和单独执行的真实执行计划也完全一致。
当单独执行时，耗时短效率很高。
原始 SQL：
SELECT
RES.*,
D.NAME_ AS processDefinitionName,
D.KEY_ AS processDefinitionKey,
D.CATEGORY_ AS processCategory,
TM.C_APPROVE_PAGE_OPEN_NEW_TAB,
TM.C_DETAILS_PAGE_OPEN_NEW_TAB,
E.BUSINESS_KEY_ AS businessKey,
E.NAME_ AS processInstanceName,
'1' AS procType,
E.START_USER_ID_ AS applyUserId
FROM
ACT_RU_TASK RES
LEFT OUTER JOIN ACT_RU_EXECUTION E ON
RES.PROC_INST_ID_ = E.ID_
AND E.RANGE_ = (1309)
LEFT OUTER JOIN ACT_RE_PROCDEF D ON
RES.PROC_DEF_ID_ = D.ID_
LEFT OUTER JOIN T_MODEL TM ON
TM.C_ACT_RE_KEY = D.KEY_
AND TM.C_PARENT_BH IS NULL
AND TM.C_DELETED = '0'
WHERE
RES.RANGE_ = (1309)
AND RES.ID_ IN (
SELECT
LINK.TASK_ID_
FROM
ACT_RU_IDENTITYLINK LINK
WHERE
LINK.USER_ID_ = ('1309B18160005828')
AND LINK.RANGE_ = (1309)
UNION ALL
SELECT
ID_
FROM
ACT_RU_TASK
WHERE
RANGE_ = (1309)
AND ASSIGNEE_ = ('1309B18160005828')
UNION ALL
SELECT
ID_
FROM
ACT_RU_TASK
WHERE
RANGE_ = (1309)
AND OWNER_ = ('1309B18160005828') )
AND RES.TENANT_ID_ IN ( ('jz'),
('SLAJ'),
('fyw'),
('ak'),
('SFJS'),
('ws'),
('sp'),
('qtty'),
('zx'),
('xf'),
('sfgzzx') )
UNION ALL SELECT
RES.*,
D.NAME_ AS processDefinitionName,
D.KEY_ AS processDefinitionKey,
D.CATEGORY_ AS processCategory,
TM.C_APPROVE_PAGE_OPEN_NEW_TAB,
TM.C_DETAILS_PAGE_OPEN_NEW_TAB,
E.BUSINESS_KEY_ AS businessKey,
E.NAME_ AS processInstanceName,
'1' AS procType,
E.START_USER_ID_ AS applyUserId
FROM
ACT_RU_TASK RES
LEFT OUTER JOIN ACT_RU_EXECUTION E ON
RES.PROC_INST_ID_ = E.ID_
AND E.RANGE_ = -9999
LEFT OUTER JOIN ACT_RE_PROCDEF D ON
RES.PROC_DEF_ID_ = D.ID_
LEFT OUTER JOIN T_MODEL TM ON
TM.C_ACT_RE_KEY = D.KEY_
AND TM.C_PARENT_BH IS NULL
AND TM.C_DELETED = '0'
WHERE
RES.RANGE_ = -9999
AND RES.ID_ IN (
SELECT
LINK.TASK_ID_
FROM
ACT_RU_IDENTITYLINK LINK
WHERE
LINK.USER_ID_ = ('1309B18160005828')
AND LINK.RANGE_ = -9999
UNION ALL
SELECT
ID_
FROM
ACT_RU_TASK
WHERE
RANGE_ = -9999
AND ASSIGNEE_ = ('1309B18160005828')
UNION ALL
SELECT
ID_
FROM
ACT_RU_TASK
WHERE
RANGE_ = -9999
AND OWNER_ = ('1309B18160005828') )
AND RES.TENANT_ID_ IN ( ('jz'),
('SLAJ'),
('fyw'),
('ak'),
('SFJS'),
('ws'),
('sp'),
('qtty'),
('zx'),
('xf'),
('sfgzzx') );
该原始 SQL 执行慢的计划如下：
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ================================================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| -------------------------------------------------------------------------------------------------- |
| |0 |UNION ALL | |97 |475262 | |
| |1 |├─NESTED-LOOP OUTER JOIN | |67 |5986 | |
| |2 |│ ├─NESTED-LOOP OUTER JOIN | |67 |4047 | |
| |3 |│ │ ├─NESTED-LOOP OUTER JOIN | |63 |2911 | |
| |4 |│ │ │ ├─NESTED-LOOP JOIN | |63 |1774 | |
| |5 |│ │ │ │ ├─SUBPLAN SCAN |VIEW3 |63 |620 | |
| |6 |│ │ │ │ │ └─HASH DISTINCT | |63 |620 | |
| |7 |│ │ │ │ │ └─UNION ALL | |194 |580 | |
| |8 |│ │ │ │ │ ├─PX COORDINATOR | |185 |561 | |
| |9 |│ │ │ │ │ │ └─EXCHANGE OUT DISTR|:EX10000 |185 |401 | |
| |10|│ │ │ │ │ │ └─TABLE RANGE SCAN|LINK(I_ACT_RU_IDENTITYLINK_ITEM1) |185 |43 | |
| |11|│ │ │ │ │ ├─TABLE RANGE SCAN |ACT_RU_TASK(ACT_IDX_TASK_ASSIGNEE)|8 |15 | |
| |12|│ │ │ │ │ └─TABLE RANGE SCAN |ACT_RU_TASK(ACT_IDX_TASK_OWNER) |1 |4 | |
| |13|│ │ │ │ └─DISTRIBUTED TABLE GET |RES |1 |18 | |
| |14|│ │ │ └─DISTRIBUTED TABLE GET |E |1 |18 | |
| |15|│ │ └─DISTRIBUTED TABLE GET |D |1 |18 | |
| |16|│ └─DISTRIBUTED TABLE RANGE SCAN |TM(INDEX_MODEL_KEY_PARENT_DELETED)|2 |29 | |
| |17|└─NESTED-LOOP OUTER JOIN | |31 |469277 | |
| |18| ├─NESTED-LOOP OUTER JOIN | |28 |468766 | |
| |19| │ ├─NESTED-LOOP OUTER JOIN | |28 |467954 | |
| |20| │ │ ├─HASH RIGHT SEMI JOIN | |27 |467478 | |
| |21| │ │ │ ├─SUBPLAN SCAN |VIEW2 |26 |91 | |
| |22| │ │ │ │ └─UNION ALL | |26 |91 | |
| |23| │ │ │ │ ├─PX COORDINATOR | |23 |70 | |
| |24| │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX20000 |23 |51 | |
| |25| │ │ │ │ │ └─TABLE RANGE SCAN |LINK(I_ACT_RU_IDENTITYLINK_ITEM1) |23 |7 | |
| |26| │ │ │ │ ├─PX COORDINATOR | |2 |14 | |
| |27| │ │ │ │ │ └─EXCHANGE OUT DISTR |:EX30000 |2 |12 | |
| |28| │ │ │ │ │ └─TABLE RANGE SCAN |ACT_RU_TASK(ACT_IDX_TASK_ASSIGNEE)|2 |7 | |
| |29| │ │ │ │ └─PX COORDINATOR | |1 |7 | |
| |30| │ │ │ │ └─EXCHANGE OUT DISTR |:EX40000 |1 |6 | |
| |31| │ │ │ │ └─TABLE RANGE SCAN |ACT_RU_TASK(ACT_IDX_TASK_OWNER) |1 |4 | |
| |32| │ │ │ └─PX COORDINATOR | |14487 |463564 | |
| |33| │ │ │ └─EXCHANGE OUT DISTR |:EX50000 |14487 |323421 | |
| |34| │ │ │ └─TABLE RANGE SCAN |RES |14487 |7924 | |
| |35| │ │ └─DISTRIBUTED TABLE GET |D |1 |18 | |
| |36| │ └─DISTRIBUTED TABLE RANGE SCAN |TM(INDEX_MODEL_KEY_PARENT_DELETED)|2 |29 | |
| |37| └─DISTRIBUTED TABLE GET |E |1 |18 | |
| ================================================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([UNION([1])], [UNION([2])], [UNION([3])], [UNION([4])], [UNION([5])], [UNION([6])], [UNION([7])], [UNION([8])], [UNION([9])], [UNION([10])], |
| [UNION([11])], [UNION([12])], [UNION([13])], [UNION([14])], [UNION([15])], [UNION([16])], [UNION([17])], [UNION([18])], [UNION([19])], [UNION([20])], [UNION([21])], |
| [UNION([22])], [UNION([23])], [UNION([24])], [UNION([25])], [UNION([26])], [UNION([27])], [UNION([28])], [UNION([29])], [UNION([30])]), filter(nil), rowset=256 |
| 1 - output([RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [D.NAME_], [D.KEY_], [D.CATEGORY_], [TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB], |
| [E.BUSINESS_KEY_], [E.NAME_], ['1'], [E.START_USER_ID_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([D.KEY_(:4)]), use_batch=true |
| 2 - output([D.KEY_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [D.NAME_], [D.CATEGORY_], [E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([RES.PROC_DEF_ID_(:1)]), use_batch=true |
| 3 - output([RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([RES.PROC_INST_ID_(:0)]), use_batch=true |
| 4 - output([RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([VIEW3.VIEW1.TASK_ID_(:3)]), use_batch=true |
| 5 - output([VIEW3.VIEW1.TASK_ID_]), filter(nil), rowset=256 |
| access([VIEW3.VIEW1.TASK_ID_]) |
| 6 - output([UNION([1])]), filter(nil), rowset=256 |
| distinct([UNION([1])]) |
| 7 - output([UNION([1])]), filter(nil), rowset=256 |
| 8 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| 9 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 10 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| access([LINK.TASK_ID_]), partitions(p217) |
| is_index_back=false, is_global_index=false, |
| range_key([LINK.RANGE_], [LINK.USER_ID_], [LINK.TASK_ID_], [LINK.ID_]), range(1309,1309B18160005828,MIN,MIN ; 1309,1309B18160005828,MAX,MAX), |
| range_cond([LINK.USER_ID_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))], [LINK.RANGE_ = 1309]) |
| 11 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| access([ACT_RU_TASK.ID_]), partitions(p217) |
| is_index_back=false, is_global_index=false, |
| range_key([ACT_RU_TASK.RANGE_], [ACT_RU_TASK.ASSIGNEE_], [ACT_RU_TASK.ID_]), range(1309,1309B18160005828,MIN ; 1309,1309B18160005828,MAX), |
| range_cond([ACT_RU_TASK.RANGE_ = 1309], [ACT_RU_TASK.ASSIGNEE_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))]) |
| 12 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| access([ACT_RU_TASK.ID_]), partitions(p217) |
| is_index_back=false, is_global_index=false, |
| range_key([ACT_RU_TASK.RANGE_], [ACT_RU_TASK.OWNER_], [ACT_RU_TASK.ID_]), range(1309,1309B18160005828,MIN ; 1309,1309B18160005828,MAX), |
| range_cond([ACT_RU_TASK.RANGE_ = 1309], [ACT_RU_TASK.OWNER_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))]) |
| 13 - output([RES.RANGE_], [RES.ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.TENANT_ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], |
| [RES.DESCRIPTION_], [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], |
| [RES.SUSPENSION_STATE_], [RES.FORM_KEY_], [RES.CLAIM_TIME_]), filter([RES.TENANT_ID_ IN (cast('jz', NVARCHAR2(2 )), cast('SLAJ', NVARCHAR2(4 )), cast('fyw', |
| NVARCHAR2(3 )), cast('ak', NVARCHAR2(2 )), cast('SFJS', NVARCHAR2(4 )), cast('ws', NVARCHAR2(2 )), cast('sp', NVARCHAR2(2 )), cast('qtty', NVARCHAR2(4 |
| )), cast('zx', NVARCHAR2(2 )), cast('xf', NVARCHAR2(2 )), cast('sfgzzx', NVARCHAR2(6 )))]), rowset=256 |
| access([GROUP_ID], [RES.RANGE_], [RES.ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.TENANT_ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.NAME_], |
| [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], |
| [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], [RES.FORM_KEY_], [RES.CLAIM_TIME_]), partitions(p217) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([RES.RANGE_], [RES.ID_]), range(MIN ; MAX), |
| range_cond([RES.RANGE_ = 1309], [RES.ID_ = :3]) |
| 14 - output([E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), filter(nil), rowset=256 |
| access([GROUP_ID], [E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), partitions(p217) |
| is_index_back=false, is_global_index=false, |
| range_key([E.RANGE_], [E.ID_]), range(MIN ; MAX), |
| range_cond([E.RANGE_ = 1309], [:0 = E.ID_]) |
| 15 - output([D.KEY_], [D.NAME_], [D.CATEGORY_]), filter(nil), rowset=256 |
| access([GROUP_ID], [D.KEY_], [D.NAME_], [D.CATEGORY_]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([D.ID_]), range(MIN ; MAX), |
| range_cond([:1 = D.ID_]) |
| 16 - output([TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB]), filter(nil), rowset=256 |
| access([GROUP_ID], [TM.C_BH], [TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([TM.C_ACT_RE_KEY], [TM.C_PARENT_BH], [TM.C_DELETED], [TM.C_BH]), range(MIN ; MAX), |
| range_cond([TM.C_PARENT_BH IS NULL], [TM.C_DELETED = to_nchar(cast('0', NVARCHAR2(1048576 )))], [TM.C_ACT_RE_KEY = :4]) |
| 17 - output([RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [D.NAME_], [D.KEY_], [D.CATEGORY_], [TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB], |
| [E.BUSINESS_KEY_], [E.NAME_], ['1'], [E.START_USER_ID_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([RES.PROC_INST_ID_(:5)]), use_batch=true |
| 18 - output([RES.PROC_INST_ID_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [D.NAME_], [D.KEY_], [D.CATEGORY_], [TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB]), filter(nil), rowset=256 |
| conds(nil), nl_params_([D.KEY_(:8)]), use_batch=true |
| 19 - output([RES.PROC_INST_ID_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_], [D.NAME_], [D.KEY_], [D.CATEGORY_]), filter(nil), rowset=256 |
| conds(nil), nl_params_([RES.PROC_DEF_ID_(:6)]), use_batch=true |
| 20 - output([RES.PROC_INST_ID_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_]), filter(nil), rowset=256 |
| equal_conds([RES.ID_ = VIEW2.TASK_ID_]), other_conds(nil) |
| 21 - output([VIEW2.TASK_ID_]), filter(nil), rowset=256 |
| access([VIEW2.TASK_ID_]) |
| 22 - output([UNION([1])]), filter(nil), rowset=256 |
| 23 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| 24 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 25 - output([LINK.TASK_ID_]), filter(nil), rowset=256 |
| access([LINK.TASK_ID_]), partitions(p251) |
| is_index_back=false, is_global_index=false, |
| range_key([LINK.RANGE_], [LINK.USER_ID_], [LINK.TASK_ID_], [LINK.ID_]), range(-9999,1309B18160005828,MIN,MIN ; -9999,1309B18160005828,MAX,MAX), |
| range_cond([LINK.USER_ID_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))], [LINK.RANGE_ = (T_OP_NEG, 9999)]) |
| 26 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| 27 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 28 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| access([ACT_RU_TASK.ID_]), partitions(p251) |
| is_index_back=false, is_global_index=false, |
| range_key([ACT_RU_TASK.RANGE_], [ACT_RU_TASK.ASSIGNEE_], [ACT_RU_TASK.ID_]), range(-9999,1309B18160005828,MIN ; -9999,1309B18160005828,MAX), |
| range_cond([ACT_RU_TASK.RANGE_ = (T_OP_NEG, 9999)], [ACT_RU_TASK.ASSIGNEE_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))]) |
| 29 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| 30 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 31 - output([ACT_RU_TASK.ID_]), filter(nil), rowset=256 |
| access([ACT_RU_TASK.ID_]), partitions(p251) |
| is_index_back=false, is_global_index=false, |
| range_key([ACT_RU_TASK.RANGE_], [ACT_RU_TASK.OWNER_], [ACT_RU_TASK.ID_]), range(-9999,1309B18160005828,MIN ; -9999,1309B18160005828,MAX), |
| range_cond([ACT_RU_TASK.RANGE_ = (T_OP_NEG, 9999)], [ACT_RU_TASK.OWNER_ = to_nchar(cast('1309B18160005828', NVARCHAR2(1048576 )))]) |
| 32 - output([RES.PROC_INST_ID_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_]), filter(nil), rowset=256 |
| 33 - output([RES.PROC_INST_ID_], [RES.ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.PROC_DEF_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], [RES.DESCRIPTION_], |
| [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], [RES.SUSPENSION_STATE_], |
| [RES.TENANT_ID_], [RES.FORM_KEY_], [RES.CLAIM_TIME_], [RES.RANGE_]), filter(nil), rowset=256 |
| is_single, dop=1 |
| 34 - output([RES.RANGE_], [RES.ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.TENANT_ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], |
| [RES.DESCRIPTION_], [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], |
| [RES.SUSPENSION_STATE_], [RES.FORM_KEY_], [RES.CLAIM_TIME_]), filter([RES.TENANT_ID_ IN (cast('jz', NVARCHAR2(2 )), cast('SLAJ', NVARCHAR2(4 )), cast('fyw', |
| NVARCHAR2(3 )), cast('ak', NVARCHAR2(2 )), cast('SFJS', NVARCHAR2(4 )), cast('ws', NVARCHAR2(2 )), cast('sp', NVARCHAR2(2 )), cast('qtty', NVARCHAR2(4 |
| )), cast('zx', NVARCHAR2(2 )), cast('xf', NVARCHAR2(2 )), cast('sfgzzx', NVARCHAR2(6 )))]), rowset=256 |
| access([RES.RANGE_], [RES.ID_], [RES.PROC_INST_ID_], [RES.PROC_DEF_ID_], [RES.TENANT_ID_], [RES.REV_], [RES.EXECUTION_ID_], [RES.NAME_], [RES.PARENT_TASK_ID_], |
| [RES.DESCRIPTION_], [RES.TASK_DEF_KEY_], [RES.OWNER_], [RES.ASSIGNEE_], [RES.DELEGATION_], [RES.PRIORITY_], [RES.CREATE_TIME_], [RES.DUE_DATE_], [RES.CATEGORY_], |
| [RES.SUSPENSION_STATE_], [RES.FORM_KEY_], [RES.CLAIM_TIME_]), partitions(p251) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([RES.RANGE_], [RES.ID_]), range(-9999,MIN ; -9999,MAX), |
| range_cond([RES.RANGE_ = (T_OP_NEG, 9999)]) |
| 35 - output([D.KEY_], [D.NAME_], [D.CATEGORY_]), filter(nil), rowset=256 |
| access([GROUP_ID], [D.KEY_], [D.NAME_], [D.CATEGORY_]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([D.ID_]), range(MIN ; MAX), |
| range_cond([:6 = D.ID_]) |
| 36 - output([TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB]), filter(nil), rowset=256 |
| access([GROUP_ID], [TM.C_BH], [TM.C_APPROVE_PAGE_OPEN_NEW_TAB], [TM.C_DETAILS_PAGE_OPEN_NEW_TAB]), partitions(p0) |
| is_index_back=true, is_global_index=false, |
| range_key([TM.C_ACT_RE_KEY], [TM.C_PARENT_BH], [TM.C_DELETED], [TM.C_BH]), range(MIN ; MAX), |
| range_cond([TM.C_PARENT_BH IS NULL], [TM.C_DELETED = to_nchar(cast('0', NVARCHAR2(1048576 )))], [TM.C_ACT_RE_KEY = :8]) |
| 37 - output([E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), filter(nil), rowset=256 |
| access([GROUP_ID], [E.BUSINESS_KEY_], [E.NAME_], [E.START_USER_ID_]), partitions(p251) |
| is_index_back=false, is_global_index=false, |
| range_key([E.RANGE_], [E.ID_]), range(MIN ; MAX), |
| range_cond([E.RANGE_ = (T_OP_NEG, 9999)], [:5 = E.ID_]) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
190 rows in set (0.207 sec)
关键诊断信息
触发条件
SQL 中 UNION ALL 超过 5 个不做代价改写导致计划不优执行耗时慢。
事前巡检
巡检 SQL 中的 UNION ALL 如果等于 5 个或者超过 5 个以上会命中此问题。
事后诊断
巡检计划中的 UNION ALL 算子个数是否超过 5 个，如果 SQL 有子查询观察计划中是否有 SEMI JOIN 的计划，如果整个 SQL 执行耗时比较快，大概是 SQL 比较简单可以忽略这个问题。
问题原因
UNION ALL/UNION 是 SQL 中常见的一种查询语法，有时候用户想要表达一个大的常量表，会使用多个 UNION ALL 的组合形式来表示，这种常量大 UNION 的 SQL 在 OceanBase 数据库内部，是有爆内存和 CPU 隐患的，现在大部分是通过禁改写来规避进一步的内存膨胀，用户的 SQL UNION ALL 正好超过 5 个，不做代价改写，OceanBase 数据库 V4.x 最新版本还是有这个限制的。目前外层 select 后加 /*+ semi_to_inner*/ 整体执行效率可以缩小到 30 ms ，SQL 中 UNION ALL 超过 5 个不做代价改写导致计划不优执行耗时慢。
问题的风险及影响
SQL 中多个 UNION ALL 因为内核的有 UNION ALL 正好超过 5 个，不做代价改写导致计划不优执行耗时慢。
适用版本
OceanBase 数据库所有版本。
解决方法及规避方式
目前外层 select 后加 /*+ semi_to_inner*/ 整体执行效率可以缩小到 30 ms。
