我一直在研究CTE，该CTE对于MS-SQL来说相当不错。我正在将其翻译为Oracle，并且可以正常工作。但是，它非常慢，并且当我查看解释计划时，运行查询时会看到合并联接笛卡尔。我正在针对Oracle 12.2运行。如果我删除
I have been working on a CTE which works pretty good for MS-SQL. I am translating it to Oracle and it works. However it is very slow and when I look at the explain plan I see "Merge Join Cartesian" when I run the query. I am running against Oracle 12.2. If I remove the
OR（...）
"OR" (...)
部分查询的速度大大提高了。但是，合并联接笛卡尔坐标仍显示在解释计划中。我已经确定问题出在查询中。
part of the query the speed is dramatically improved. However the "Merge Join Cartesian" still shows up in the explain plan. I have determined where the problem is in the query. The
和（....）或（...）
"and" (....) "or" (...)
是慢度的来源。我需要这两个部分，因为有不同的路径，因此摆脱一个或另一个并不是真正的选择。我曾尝试重写CTE的那部分，但运气不佳。我的第一次尝试是在递归部分中包含第二个查询。但是，Oracle仅在CTE的递归部分中允许一个查询。我也尝试将其分解为较小的部分，但是我的尝试失败了。对于在本CTE中重写递归部分的任何建议，我们将不胜感激。
in the recursive query is the source of the slowness. I need both sections because there are different paths so getting rid of one or the other is not really an option. I have tried to rewrite that part of CTE but I am not having much luck. My first try was to include a second query in the recursive section. However Oracle ONLY allows one query in the recursive section of the CTE. I have also tried to break this down into smaller pieces but my attempts have failed. I would appreciate any suggestions on rewriting the recursive section in this CTE.
AND ( myCTE3.SubWorkflowBaseId <> '0000000000000000' and myCTE3.SubWorkflowBaseId is not null and myCTE3.JoinColumn = wfb.WorkflowBaseId and wfb.RevOfRcdId = wf.WorkflowId and wf.workflowid = wfs.WorkflowId ) OR ( myCTE3.SubWorkflowId <> '0000000000000000' and myCTE3.SubWorkflowId is not null and myCTE3.JoinColumn = wf.workflowid --and wf.workflowid = wfs.SubWorkflowId and wf.workflowid = wfs.WorkflowId and wf.WorkflowBaseId = wfb.WorkflowBaseId )我已经在SQLFiddle上发布了CTE和示例数据。我忘了在最初的帖子中发布预期的结果。记录应返回的顺序如下。工作流程中有一些步骤指向子工作流程，但这些步骤应从结果中排除。
I have posted my CTE and sample data on SQLFiddle. I forgot to post the expected results with my initial post. The order the records should come back is the following. There are steps in a workflow which point to subworkflow but those should be excluded from the results.
步骤1a->步骤2a->步骤3a->步骤3b->步骤1c。
Step-1a -> Step-2a -> Step-3a -> Step-3b -> Step-1c.
示例数据和CTE
Plan hash value: 3402776882 ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 2 | 4074 | 76 (2)| 00:00:01 | | 1 | SORT ORDER BY | | 2 | 4074 | 76 (2)| 00:00:01 | |* 2 | VIEW | | 2 | 4074 | 75 (0)| 00:00:01 | | 3 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | |* 4 | HASH JOIN | | 1 | 119 | 6 (0)| 00:00:01 | |* 5 | TABLE ACCESS FULL | WORKFLOWSTEP2 | 1 | 102 | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL | WORKFLOW2 | 1 | 17 | 3 (0)| 00:00:01 | | 7 | NESTED LOOPS | | 1 | 2239 | 69 (0)| 00:00:01 | | 8 | BUFFER SORT (REUSE) | | | | | | | 9 | MERGE JOIN CARTESIAN | | 1 | 170 | 9 (0)| 00:00:01 | | 10 | MERGE JOIN CARTESIAN | | 1 | 136 | 6 (0)| 00:00:01 | | 11 | TABLE ACCESS FULL | WORKFLOWSTEP2 | 1 | 102 | 3 (0)| 00:00:01 | | 12 | BUFFER SORT | | 3 | 102 | 3 (0)| 00:00:01 | | 13 | TABLE ACCESS FULL | WORKFLOWBASE2 | 3 | 102 | 3 (0)| 00:00:01 | | 14 | BUFFER SORT | | 3 | 102 | 6 (0)| 00:00:01 | | 15 | TABLE ACCESS FULL | WORKFLOW2 | 3 | 102 | 3 (0)| 00:00:01 | |* 16 | RECURSIVE WITH PUMP | | | | | | ------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("MYCTE3"."STEPTYPE"<>2) 4 - access("WF"."WORKFLOWID"="WFS"."WORKFLOWID") 5 - filter("WFS"."WORKFLOWID"='1100000000000000') 6 - filter("WF"."WORKFLOWID"='1100000000000000') 16 - filter("MYCTE3"."JOINCOLUMN" IS NOT NULL AND "MYCTE3"."SUBWORKFLOWBASEID"<>'0000000000000000' AND "MYCTE3"."SUBWORKFLOWBASEID" IS NOT NULL AND "MYCTE3"."JOINCOLUMN"="WFB"."WORKFLOWBASEID" AND "WFB"."REVOFRCDID"="WF"."WORKFLOWID" AND "WF"."WORKFLOWID"="WFS"."WORKFLOWID" OR "MYCTE3"."SUBWORKFLOWID"<>'0000000000000000' AND "MYCTE3"."SUBWORKFLOWID" IS NOT NULL AND "MYCTE3"."JOINCOLUMN"="WF"."WORKFLOWID" AND "WF"."WORKFLOWID"="WFS"."WORKFLOWID" AND "WF"."WORKFLOWBASEID"="WFB"."WORKFLOWBASEID")推荐答案
我尝试将其重写为：
with MyCTE3 (WorkflowStepName, Depth, WorkflowId, WorkflowStepId, SubWorkflowBaseId, SubWorkflowId, Sequence, StepType, JoinColumn, DisplayOrder) as ( SELECT wfs.WorkflowStepName ,1 as Depth , wfs.WorkflowId , wfs.WorkflowStepId , wfs.SubWorkflowBaseId , wfs.SubWorkflowId , wfs.Sequence , wfs.StepType , case when wfs.SubWorkflowBaseId = '0000000000000000' then wfs.SubworkflowId --when wfs.SubWorkflowBaseId is null then wfs.SubworkflowId when wfs.SubWorkflowId = '0000000000000000' then wfs.SubWorkflowBaseId --when wfs.SubWorkflowId is null then wfs.SubWorkflowBaseId end as JoinColumn ,1 || '.' || CAST(wfs.Sequence AS VARCHAR(255)) AS DisplayOrder --, to_clob(wfs.WorkflowId) as ProcessedWorkflow FROM WorkflowStep2 wfs, Workflow2 wf WHERE wfs.WorkflowId = '1100000000000000' and wf.WorkflowId = wfs.WorkflowId union all SELECT wfs.WorkflowStepName , Depth+1 , wfs.WorkflowId , wfs.WorkflowStepId , wfs.SubWorkflowBaseId , wfs.SubWorkflowId , wfs.Sequence , wfs.StepType , case when wfs.SubWorkflowBaseId = '0000000000000000' then wfs.SubworkflowId --when wfs.SubWorkflowBaseId is null then wfs.SubworkflowId when wfs.SubWorkflowId = '0000000000000000' then wfs.SubWorkflowBaseId --when wfs.SubWorkflowId is null then wfs.SubWorkflowBaseId end as JoinColumn ,DisplayOrder || '.' || CAST(Depth+1 AS VARCHAR(255)) || CAST(wfs.Sequence AS VARCHAR(255)) as DisplayOrder --,to_clob(myCTE3.ProcessedWorkflow) || ',' || to_clob(wfs.WorkflowId) as ProcessedWorkflow FROM workflowbase2 wfb, workflowstep2 wfs, workflow2 wf, MyCTE3 where myCTE3.JoinColumn is not null -- and (','+to_clob(myCTE3.ProcessedWorkflow) +',' not like '%,'+to_clob(wfs.WorkflowId)+',%') -- and (dbms_lob.instr(myCTE3.ProcessedWorkflow,wfs.WorkflowId) = 0) AND ( myCTE3.SubWorkflowBaseId <> '0000000000000000' and myCTE3.SubWorkflowBaseId is not null and myCTE3.JoinColumn = wfb.WorkflowBaseId and wfb.RevOfRcdId = wf.WorkflowId and wf.workflowid = wfs.WorkflowId ) ), mcte2(WorkflowStepName, Depth, WorkflowId, WorkflowStepId, SubWorkflowBaseId, SubWorkflowId, Sequence, StepType, JoinColumn, DisplayOrder) AS ( SELECT wfs.WorkflowStepName ,1 as Depth , wfs.WorkflowId , wfs.WorkflowStepId , wfs.SubWorkflowBaseId , wfs.SubWorkflowId , wfs.Sequence , wfs.StepType , case when wfs.SubWorkflowBaseId = '0000000000000000' then wfs.SubworkflowId --when wfs.SubWorkflowBaseId is null then wfs.SubworkflowId when wfs.SubWorkflowId = '0000000000000000' then wfs.SubWorkflowBaseId --when wfs.SubWorkflowId is null then wfs.SubWorkflowBaseId end as JoinColumn ,1 || '.' || CAST(wfs.Sequence AS VARCHAR(255)) AS DisplayOrder --, to_clob(wfs.WorkflowId) as ProcessedWorkflow FROM WorkflowStep2 wfs, Workflow2 wf WHERE wfs.WorkflowId = '1100000000000000' and wf.WorkflowId = wfs.WorkflowId union all SELECT wfs.WorkflowStepName , Depth+1 , wfs.WorkflowId , wfs.WorkflowStepId , wfs.SubWorkflowBaseId , wfs.SubWorkflowId , wfs.Sequence , wfs.StepType , case when wfs.SubWorkflowBaseId = '0000000000000000' then wfs.SubworkflowId --when wfs.SubWorkflowBaseId is null then wfs.SubworkflowId when wfs.SubWorkflowId = '0000000000000000' then wfs.SubWorkflowBaseId --when wfs.SubWorkflowId is null then wfs.SubWorkflowBaseId end as JoinColumn ,DisplayOrder || '.' || CAST(Depth+1 AS VARCHAR(255)) || CAST(wfs.Sequence AS VARCHAR(255)) as DisplayOrder --,to_clob(myCTE3.ProcessedWorkflow) || ',' || to_clob(wfs.WorkflowId) as ProcessedWorkflow FROM workflowbase2 wfb, workflowstep2 wfs, workflow2 wf, MyCTE3 where myCTE3.JoinColumn is not null AND ( myCTE3.SubWorkflowId <> '0000000000000000' and myCTE3.SubWorkflowId is not null and myCTE3.JoinColumn = wf.workflowid --and wf.workflowid = wfs.SubWorkflowId and wf.workflowid = wfs.WorkflowId and wf.WorkflowBaseId = wfb.WorkflowBaseId ) ) select WorkFlowStepName, DisplayOrder --, DisplayOrder, ProcessedWorkflow from MyCTE3 where MYCTE3.StepType <> 2 UNION ALL select WorkFlowStepName, DisplayOrder --, DisplayOrder, ProcessedWorkflow from MyCTE3 where MYCTE3.StepType <> 2 order by DisplayOrder ;SQLFiddle演示
SQLFiddle Demo
我使用了或扩展
OR扩展是可用于优化析取查询（包含OR子句的查询）的转换。 OR扩展的基本思想是将包含析取的查询转换为两个或多个分支的UNION ALL查询的形式。这是通过将析取运算拆分成各个组件并将每个组件与UNION ALL查询的一个分支相关联来完成的。
OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain OR clauses). The basic idea in OR expansion is to transform a query containing disjunctions into the form of a UNION ALL query of two or more branches. This is done by splitting the disjunction into its components and associating each component with a branch of a UNION ALL query.
更多推荐
尝试在Oracle CTE中重构递归查询？
发布评论