Query optimization [Oracle]

Question

How can I optimize the performance of this query?

SELECT Count(DISTINCT DT.id) 
FROM   pcwdeptrans DT 
       INNER JOIN pcwitemtotal IT 
               ON DT.id = IT.deposittransid 
       LEFT OUTER JOIN pcwdepreceipt DR 
                    ON DR.deposittransid = DT.id 
WHERE  (( ( DT.statecode IN ( :1, :2, :3, :4, 
                              :5, :6, :7, :8 ) 
             OR ( DT.statecode IN ( :9 ) 
                  AND IT.statecode = :10 ) ) 
          AND DR.requesttime >= :11 
          AND DR.requesttime <= :12 
          AND DR.userid = :13 ))

Please help me with the right syntax if you think its incorrect.

But this is an application query, identified from the AWR report as part of performance analysis

Excerpt from AWR:

Top SQL with TOP Events

Its Execution plan from test DB (prod will be different):

Execution plan of the query

Your `WHERE` clause is turning the outer join into an inner join -- if I'm reading the parentheses correctly. — Gordon Linoff, Dec 05 '18 at 14:59
I agree with Gordon. You should make sure your query is **functionally correct** before you start tuning it. — APC, Dec 05 '18 at 15:03

bw_üezi · Answer 1 · 2018-12-07T11:11:13.477

Join together with Where can be slow. following could be faster (but not the same... see comments and rethink if you need inner or outer join...)

SELECT Count(DISTINCT DT.id) 
FROM pcwdeptrans DT 
  INNER JOIN pcwitemtotal IT 
      ON DT.id = IT.deposittransid 
     AND ( DT.statecode IN ( :1, :2, :3, :4, :5, :6, :7, :8 ) 
       OR ( DT.statecode IN ( :9 ) AND IT.statecode = :10 ) )
  LEFT OUTER JOIN pcwdepreceipt DR 
      ON DR.deposittransid = DT.id 
     AND DR.requesttime >= :11 
     AND DR.requesttime <= :12 
     AND DR.userid = :13

edit: The discussion whether it should be an inner or outer join was already started in the question's comments before. Transforming the question's outer join to a inner join with the where clause may or may not be intendet.

SELECT Count(DISTINCT DT.id) 
FROM pcwdeptrans DT 
  INNER JOIN pcwitemtotal IT 
      ON DT.id = IT.deposittransid 
     AND ( DT.statecode IN ( :1, :2, :3, :4, :5, :6, :7, :8 ) 
       OR ( DT.statecode IN ( :9 ) AND IT.statecode = :10 ) )
  INNER JOIN pcwdepreceipt DR 
      ON DR.deposittransid = DT.id 
     AND DR.requesttime >= :11 
     AND DR.requesttime <= :12 
     AND DR.userid = :13

doesn't this change the query logic? the filtering in WHERE clause effectively made the join with pcwdepreceipt an INNER JOIN — kasparg, Dec 05 '18 at 18:20

Query optimization [Oracle]

Excerpt from AWR:

Its Execution plan from test DB (prod will be different):

1 Answers1