Hi,
The following sql query is taking more time for execution. I want to tune this query.
DB and Version : Oracle 9.2
SELECT DISTINCT A.MEMO_ID,A.MEMO_NO
FROM TB_EB_REQ_MEMO A,TB_EB_REQ_MEMO_DTS B
WHERE A.DELETED_FLAG = 'N'
AND B.MEMO_ID = A.MEMO_ID
AND A.MEMO_DATE
BETWEEN TO_DATE('01/04/2009', 'DD/MM/YYYY')
AND TO_DATE('10/11/2009','DD/MM/YYYY')
AND B.MEMO_DTS_PK
IN (
SELECT B.MEMO_DTS_PK
FROM TB_EB_REQ_MEMO A, TB_EB_REQ_MEMO_DTS B
WHERE A.DELETED_FLAG = 'N'
AND B.MEMO_ID = A.MEMO_ID
MINUS
SELECT MEMO_DTS_PK
FROM (
SELECT SUM(D.PO_QTY) AS PQ ,SUM(B.REQ_QTY) AS RQ,B.MEMO_DTS_PK
FROM TB_EB_REQ_MEMO A,TB_EB_REQ_MEMO_DTS B,TB_EB_PO C,TB_EB_PO_DTS D
WHERE A.DELETED_FLAG = 'N'
AND C.DELETED_FLAG = 'N'
AND B.MEMO_ID = A.MEMO_ID
AND D.PO_ID = C.PO_ID
AND C.MEMO_ID = B.MEMO_ID
AND B.CA_ITEM_ID = D.CA_ITEM_ID
AND B.ITEM_ID = D.ITEM_ID
GROUP BY B.MEMO_DTS_PK
)
WHERE PQ > RQ
OR PQ = RQ
)
ORDER BY MEMO_ID
execution plan for this query is
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2611 | 109K| 870 |
| 1 | SORT UNIQUE | | 2611 | 109K| 806 |
|* 2 | HASH JOIN | | 2611 | 109K| 743 |
|* 3 | HASH JOIN | | 3479 | 101K| 134 |
|* 4 | TABLE ACCESS FULL | TB_EB_REQ_MEMO | 880 | 19360 | 57 |
| 5 | TABLE ACCESS FULL | TB_EB_REQ_MEMO_DTS | 33469 | 261K| 76 |
| 6 | VIEW | VW_NSO_1 | 25116 | 318K| 606 |
| 7 | MINUS | | | | |
| 8 | SORT UNIQUE | | 25116 | 318K| |
|* 9 | HASH JOIN | | 25116 | 318K| 136 |
|* 10 | TABLE ACCESS FULL | TB_EB_REQ_MEMO | 6355 | 31775 | 57 |
| 11 | TABLE ACCESS FULL | TB_EB_REQ_MEMO_DTS | 33469 | 261K| 76 |
| 12 | SORT UNIQUE | | 1 | 39 | |
| 13 | VIEW | | 1 | 39 | 263 |
|* 14 | FILTER | | | | |
| 15 | SORT GROUP BY | | 1 | 43 | 263 |
| 16 | NESTED LOOPS | | 1 | 43 | 236 |
|* 17 | HASH JOIN | | 1 | 38 | 235 |
|* 18 | HASH JOIN | | 1179 | 34191 | 155 |
| 19 | TABLE ACCESS FULL | TB_EB_PO_DTS | 33165 | 421K| 60 |
| 20 | TABLE ACCESS FULL | TB_EB_REQ_MEMO_DTS | 33469 | 522K| 76 |
|* 21 | TABLE ACCESS FULL | TB_EB_PO | 6495 | 58455 | 79 |
|* 22 | TABLE ACCESS BY INDEX ROWID| TB_EB_REQ_MEMO | 1 | 5 | 1 |
|* 23 | INDEX UNIQUE SCAN | PK_MEMO | 2 | | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."MEMO_DTS_PK"="VW_NSO_1"."$nso_col_1")
3 - access("B"."MEMO_ID"="A"."MEMO_ID")
4 - filter("A"."DELETED_FLAG"='N' AND "A"."MEMO_DATE">=TO_DATE('2009-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "A"."MEMO_DATE"<=TO_DATE('2009-11-10 00:00:00'
, 'yyyy-mm-dd hh24:mi:ss'))
9 - access("B"."MEMO_ID"="A"."MEMO_ID")
10 - filter("A"."DELETED_FLAG"='N')
14 - filter(SUM("D"."PO_QTY")>SUM("B"."REQ_QTY") OR SUM("D"."PO_QTY")=SUM("B"."REQ_QTY"
))
17 - access("D"."PO_ID"="C"."PO_ID" AND "C"."MEMO_ID"="B"."MEMO_ID")
18 - access("B"."CA_ITEM_ID"="D"."CA_ITEM_ID" AND "B"."ITEM_ID"="D"."ITEM_ID")
21 - filter("C"."DELETED_FLAG"='N')
22 - filter("A"."DELETED_FLAG"='N')
23 - access("B"."MEMO_ID"="A"."MEMO_ID")
Note: cpu costing is off
Could you help to solve this problem?
Thanks in advance....