CODEWITHSUNDEEP
sqloracle-database
Ildelian
Ildelian

Reputation: 1318

Oracle Left Join takes more than 30 Hours

A question about two Queries:

Query A:

SELECT ******
FROM WORK_ORDERS w
  LEFT JOIN (
      SELECT crew.WO_ID, EMP.DOCUMENT_NUMBER|| '-'|| emp.FIRST_NAME|| ' '|| emp.LAST_NAME helpers 
      FROM WORK_ORDER_CREW_ASSIGMENTS crew
        INNER JOIN EMPLOYEE_CREWS employeeCrew
          ON employeeCrew.CREW_ID = crew.CREW_ID AND employeeCrew.IS_RESPONSIBLE = 'N'
        INNER JOIN EMPLOYEES emp ON EMP.id = EMPLOYEECREW.EMPLOYEE_ID
      WHERE crew.IS_ACTIVE = 'S'
    ) crewHelpers
    ON (w.id = crewHelpers.wo_id)
      and w.CREATION_DATE between
        to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')
        and  to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

Query B:

SELECT ******
FROM WORK_ORDERS w
  LEFT JOIN WORK_ORDER_CREW_ASSIGMENTS crew
    ON CREW.WO_ID = w.ID
  INNER JOIN EMPLOYEE_CREWS employeeCrew
    ON employeeCrew.CREW_ID = crew.CREW_ID
      AND employeeCrew.IS_RESPONSIBLE = 'N'
  INNER JOIN EMPLOYEES emp
    ON EMP.id = EMPLOYEECREW.EMPLOYEE_ID
WHERE crew.IS_ACTIVE = 'S'
  and w.CREATION_DATE between
    to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')
    and to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

Query A: -> Time of Execution: More than 30 Hours. -> Query PLan Cost: 3156361

Query B: -> Time of Execution: 15 Minutes. -> Query PLan Cost: 296107

I think the left join works the same in both queries, and it takes the same data. How can there be so much difference in time and cost from one to another?

Upvotes: 0

Views: 97

Answers (3)

Ildelian
Ildelian

Reputation: 1318

Problem solved.

This was incorrect and done a lot of nested loops:

LEFT JOIN (SELECT crew.WO_ID, EMP.DOCUMENT_NUMBER|| '-'|| emp.FIRST_NAME|| ' '|| emp.LAST_NAME helpers 
FROM WORK_ORDER_CREW_ASSIGMENTS crew
INNER JOIN EMPLOYEE_CREWS employeeCrew ON employeeCrew.CREW_ID = crew.CREW_ID AND employeeCrew.IS_RESPONSIBLE = 'N'
INNER JOIN EMPLOYEES emp ON EMP.id = EMPLOYEECREW.EMPLOYEE_ID WHERE crew.IS_ACTIVE = 'S'
) crewHelpers ON (w.id = crewHelpers.wo_id)
->**AND**<- w.CREATION_DATE between to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss') and  to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

The fix:

LEFT JOIN (SELECT crew.WO_ID, EMP.DOCUMENT_NUMBER|| '-'|| emp.FIRST_NAME|| ' '|| emp.LAST_NAME helpers 
FROM WORK_ORDER_CREW_ASSIGMENTS crew
INNER JOIN EMPLOYEE_CREWS employeeCrew ON employeeCrew.CREW_ID = crew.CREW_ID AND employeeCrew.IS_RESPONSIBLE = 'N'
INNER JOIN EMPLOYEES emp ON EMP.id = EMPLOYEECREW.EMPLOYEE_ID WHERE crew.IS_ACTIVE = 'S'
) crewHelpers ON (w.id = crewHelpers.wo_id)
->**WHERE**<- w.CREATION_DATE between to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss') and  to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

Upvotes: 0

BobC
BobC

Reputation: 4434

Without any execution plans to go on at this time, this is somewhat of a guess.

I think the issue is the the LEFT OUTER join on a the data range:

 ON (w.id = crewHelpers.wo_id)
AND w.CREATION_DATE BETWEEN to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss') 
                        AND to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

While there is nothing syntactically wrong with this, what it does mean is that the rows that do not meet the date range ar filtered out only to be added back by the outer join. From a plan perspective, all the filtering is done post (outer) join.

Can you try with the following

 ON (w.id = crewHelpers.wo_id)
WHERE w.CREATION_DATE BETWEEN to_date('01/01/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss') 
                          AND to_date('01/04/2016 00:00:00','DD/MM/YYYY HH24:Mi:ss')

It may be necessary to see which of these constructs does indeed give the correct results

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

You are comparing apples to orages, because these two queries are different
- they give different results.

Since they are different, so it is not a big surprise that their plans and execution times differ.

Please examine the below simplified example to understand why they are different: http://sqlfiddle.com/#!9/93e681/4

CREATE TABLE WORK_ORDERS(
   id int
);

INSERT INTO WORK_ORDERS VALUES (1),(2),(3);

CREATE TABLE WORK_ORDER_CREW(
  wo_id int,
  IS_ACTIVE char(1)
);

INSERT INTO WORK_ORDER_CREW VALUES(1,'Y'),(3,'S');

SELECT *
FROM WORK_ORDERS w
LEFT JOIN WORK_ORDER_CREW crew
ON w.id = crew.wo_id
WHERE crew.IS_ACTIVE = 'S'
;
| id | wo_id | IS_ACTIVE |
|----|-------|-----------|
|  3 |     3 |         S |

SELECT *
FROM WORK_ORDERS w
LEFT JOIN (
  SELECT * FROM WORK_ORDER_CREW crew
  WHERE crew.IS_ACTIVE = 'S'
) crew
ON w.id = crew.wo_id;
| id |  wo_id | IS_ACTIVE |
|----|--------|-----------|
|  1 | (null) |    (null) |
|  2 | (null) |    (null) |
|  3 |      3 |         S |

Upvotes: 2

Related Questions