Could you have a look at the below queries and its execution plan and suggest tuning recommendations on query rewrite and index changes etc to reduce the cost and better the performance if any.
Query 1:
======
SELECT SUM(Nvl(a.depcount, 0) + Nvl(b.depcount, 0)) AS depcount
FROM (SELECT 1 AS classid,
**Count**(DISTINCT dt.id) AS depcount
FROM pcwdeptrans DT
inner join pcwitemtotal IT
ON dt.id \= it.deposittransid
left outer join pcwdepreceipt DR
ON dr.deposittransid \= dt.id
WHERE dt.bankaccountid IN ( 200000000001 )
AND (( ( dt.statecode IN ( 2, 3, 5, 6,
7, 8, 12, 13 )
OR ( dt.statecode IN ( 2 )
AND it.statecode \= 3 ) )
AND dr.requesttime \>= **To\_date**('20190124000000',
'YYYYMMDDHH24MISS')
AND dr.requesttime \<= **To\_date**('20190324000000',
'YYYYMMDDHH24MISS') ))) a
left join (SELECT 1 AS classid,
**Count**(DISTINCT dt.id) AS depcount
FROM pcwdeptrans DT
inner join pcwitemtotal IT
ON dt.id \= it.deposittransid
left outer join pcwdepreceipt DR
ON dr.deposittransid \= dt.id
WHERE dt.bankaccountid IN ( 200000000001 )
AND ( dt.statecode \= 2
AND it.statecode \= 3
AND it.createdate \>= **To\_date**('20190124000000',
'YYYYMMDDHH24MISS')
)
AND it.createdate \<= **To\_date**('20190324000000',
'YYYYMMDDHH24MISS')) b
ON a.classid \= b.classid;
Execution Plan:
===============
Query 2:
========
SELECT SUM(Nvl(a.depcount, 0) + Nvl(b.depcount, 0)) AS depCount
FROM (SELECT 1 AS classid,
**Count**(DISTINCT DT.id) AS depCount
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 ( 2, 3, 5, 6,
7, 8, 12, 13 )
OR ( DT.statecode IN ( 2 )
AND IT.statecode \= 3 ) )
AND DR.requesttime \>= '2018-01-24 00:00:00'
AND DR.requesttime \<= '2018-10-24 23:59:59'
AND DR.userid \= 2300000320757 ))) a
left join (SELECT 1 AS classid,
**Count**(DISTINCT DT.id) AS depCount
FROM pcwdeptrans DT
inner join pcwitemtotal IT
ON DT.id \= IT.deposittransid
left outer join pcwdepreceipt DR
ON DR.deposittransid \= DT.id