How to optimize query which has or in left join clause?

Question

When I execute my code, it spends many hours and it has not been finished. And I know that the problem is using 'or' in left join clause. So I want to know how to rewrite this code that is can be executed.

SELECT  WFTD.RELA
            , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
            , WFTCS.CREATE_DATE AS CONSULT_DATE
            , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
FROM TNH_WF WF
INNER JOIN TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID AND WFT.OWNER_ATONEMENT IS NOT NULL AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
INNER JOIN TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID AND WFTCS.WORK_TRAY_ID = 'N07' 

LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'

LEFT JOIN TND_WF_TASK_DETAIL WFTD

ON (
            (
                (WFTD.WF_TASK_ID = WFT.PARENT_TASK 
                AND WFT.WORK_TRAY_ID IN ('N07') 
                AND WFTD.CONSIDER_RESULT_CODE LIKE '%Consult%') 
            
            OR 
                (WFTD.NEXT_TASK_ID = WFT.WF_TASK_ID 
                AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')) 
            AND 
                WFT.STATUS IN (0,1,2)
            )
        
        OR 
        (WFTD.WF_TASK_ID = WFT.WF_TASK_ID 
        AND WFT.STATUS IN (3)))
    
    AND WFTD.POL_COV_FLAG = 'P'

Answer 1

in this optimized query i did following things:

 1. I've separated the JOIN conditions into separate LEFT JOIN clauses
    to improve readability.
 2. I've simplified the WHERE clause by using an explicit check for
    WFTD.WF_TASK_ID IS NOT NULL OR WFT.STATUS = 3 instead of the complex
    condition in the original query.
 3. Make sure proper indexing is in place for the join columns and the
    columns used in WHERE conditions for optimal performance.

    SELECT  
    WFTD.RELA,
    WFTCS.PARENT_TASK AS CONSULT_TASK_ID,
    WFTCS.CREATE_DATE AS CONSULT_DATE,
    WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
FROM 
    TNH_WF WF
INNER JOIN 
    TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID 
        AND WFT.OWNER_ATONEMENT IS NOT NULL 
        AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
INNER JOIN 
    TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID 
        AND WFTCS.WORK_TRAY_ID = 'N07' 
LEFT JOIN 
    TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID 
        AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'
LEFT JOIN 
    TND_WF_TASK_DETAIL WFTD ON WFTD.WF_TASK_ID = WFT.PARENT_TASK 
        AND WFT.WORK_TRAY_ID IN ('N07') 
        AND WFTD.CONSIDER_RESULT_CODE LIKE '%Consult%'
        AND WFT.STATUS IN (0, 1, 2)
        AND WFTD.POL_COV_FLAG = 'P'

LEFT JOIN 
    TND_WF_TASK_DETAIL WFTD_NEXT ON WFTD_NEXT.NEXT_TASK_ID = WFT.WF_TASK_ID 
        AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06') 
        AND WFT.STATUS IN (0, 1, 2)
        AND WFTD_NEXT.POL_COV_FLAG = 'P'
WHERE 
    (WFTD.WF_TASK_ID IS NOT NULL OR WFT.STATUS = 3)

Answer 2

While we can't be certain without seeing your execution data, it is very likely due to your join to WFTD, as you've rightly guessed. There are a number of approaches to deal with this. From least rewrite to greatest:

1. Add a /*+ USE_CONCAT */ hint to the query block (right after the SELECT keyword at the top). That may push Oracle into rewriting your query for you as a series of UNION (or UNION ALL followed by a DISTINCT sort) blocks, each with a definite equijoin condition to eliminate the OR conditions. But as you have some compounding and IN operators as well, Oracle may struggle with it. Worth a try as it'd require no change to the code other than the hint.

2. If the join to WFTD is a 1:1 join (at most only one row from WFTD could possibly match any of the possible join conditions), you can simply join to the table three times and then take the columns you need from whichever found results:

          SELECT  COALESCE(WFTD1.RELA,WFTD2.RELA,WFTD3.RELA) AS RELA
                      , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
                      , WFTCS.CREATE_DATE AS CONSULT_DATE
                      , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
          FROM TNH_WF WF
          INNER JOIN TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID AND WFT.OWNER_ATONEMENT IS NOT NULL AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
          INNER JOIN TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID AND WFTCS.WORK_TRAY_ID = 'N07' 
   
          LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'
   
          LEFT JOIN TND_WF_TASK_DETAIL WFTD1 ON WFTD1.WF_TASK_ID = WFT.PARENT_TASK 
                                            AND WFTD1.CONSIDER_RESULT_CODE LIKE '%Consult%'
                                            AND WFT.WORK_TRAY_ID IN ('N07') 
                                            AND WFT.STATUS IN (0,1,2)
                                            AND WFTD1.POL_COV_FLAG = 'P'
   
          LEFT JOIN TND_WF_TASK_DETAIL WFTD2 ON WFTD2.NEXT_TASK_ID = WFT.WF_TASK_ID 
                                            AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')
                                            AND WFT.STATUS IN (0,1,2)
                                            AND WFTD2.POL_COV_FLAG = 'P'
   
          LEFT JOIN TND_WF_TASK_DETAIL WFTD3 ON WFTD3.WF_TASK_ID = WFT.WF_TASK_ID 
                                            AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')
                                            AND WFT.STATUS IN (3)       
                                            AND WFTD3.POL_COV_FLAG = 'P'
   

3. Lastly, if Oracle was unable to rewrite it for you with the hint and the join to WFTD might not be 1:1 (which could result in row expansion [too many rows] in the option #2 above), then you can rewrite the whole query with UNION. Copy paste it three times joined by UNION, collapsing the join to one of the three in each one:

        SELECT  WFTD.RELA
                    , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
                    , WFTCS.CREATE_DATE AS CONSULT_DATE
                    , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
        FROM TNH_WF WF
        INNER JOIN TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID AND WFT.OWNER_ATONEMENT IS NOT NULL AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
        INNER JOIN TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID AND WFTCS.WORK_TRAY_ID = 'N07' 
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD ON WFTD.WF_TASK_ID = WFT.PARENT_TASK 
                                        AND WFTD.CONSIDER_RESULT_CODE LIKE '%Consult%'
                                        AND WFT.WORK_TRAY_ID IN ('N07') 
                                        AND WFT.STATUS IN (0,1,2)
                                        AND WFTD.POL_COV_FLAG = 'P'
        UNION
        SELECT  WFTD.RELA
                    , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
                    , WFTCS.CREATE_DATE AS CONSULT_DATE
                    , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
        FROM TNH_WF WF
        INNER JOIN TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID AND WFT.OWNER_ATONEMENT IS NOT NULL AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
        INNER JOIN TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID AND WFTCS.WORK_TRAY_ID = 'N07' 
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD  ON WFTD.NEXT_TASK_ID = WFT.WF_TASK_ID 
                                          AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')
                                          AND WFT.STATUS IN (0,1,2)
                                          AND WFTD.POL_COV_FLAG = 'P'
        UNION
        SELECT  WFTD.RELA
                    , WFTCS.PARENT_TASK AS CONSULT_TASK_ID
                    , WFTCS.CREATE_DATE AS CONSULT_DATE
                    , WFTD_THIS_TASK.APPROVED_RESULT_DATE AS THISTASK_APPROVED_RESULT_DATE
        FROM TNH_WF WF
        INNER JOIN TND_WF_TASK WFT ON WFT.WF_ID = WF.WF_ID AND WFT.OWNER_ATONEMENT IS NOT NULL AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06', 'N07')
        INNER JOIN TND_WF_TASK WFTCS ON WFTCS.WF_ID = WFT.WF_ID AND WFTCS.WORK_TRAY_ID = 'N07' 
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD_THIS_TASK ON WFTD_THIS_TASK.WF_TASK_ID = WFT.WF_TASK_ID AND WFTD_THIS_TASK.POL_COV_FLAG = 'P'
   
        LEFT JOIN TND_WF_TASK_DETAIL WFTD ON WFTD.WF_TASK_ID = WFT.WF_TASK_ID 
                                          AND WFT.WORK_TRAY_ID IN ('N02', 'N03', 'N06')
                                          AND WFT.STATUS IN (3)       
                                          AND WFTD.POL_COV_FLAG = 'P'