--- title: SQL 包含多个 union all 执行耗时慢的原因和解决方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 SQL 包含多个 union all 执行耗时慢的原因和解决方法相关的常见问题和使用技巧，帮助您快速解决 SQL 包含多个 union all 执行耗时慢的原因和解决方法的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 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 毫秒。 1. union 的上半段和下半段的执行语句完全一致，本质只是条件值不一致，而且通过两个单独的逻辑执行计划和单独执行的真实执行计划也完全一致。 2. 当单独执行时，耗时短效率很高。 原始 SQL： ```shell 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 执行慢的计划如下： ```shell +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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。 Previous [数据库执行计划频繁跳变问题排查与解决](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000005438096) Next [业务 SQL 响应时长偶发连续超长](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003899663) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线