尝试在Oracle CTE中重构递归查询？ [英] Trying to refactor the recursive query in an Oracle CTE?

查看：81
本文介绍了尝试在Oracle CTE中重构递归查询？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我一直在研究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中重构递归查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆