Hi there,

I was wondering if anyone could help me tune the following query.

As it stands this query never produces any results, it takes so long that it has to be canceled.

I have included the explain plan for the query and a list of the row counts for each table included.

The user is urgently requiring this information

Many thanks

Allie

SELECT DISTINCT
B.BUSINESS_UNIT,
A.TRANSACTION_GROUP,
TO_CHAR(B.TRANSACTION_DATE,'YYYY-MM-DD'),
TO_CHAR(A.ACCOUNTING_DT,'YYYY-MM-DD'),
A.ACCTG_LINE_NO,
B.INV_ITEM_ID,
B.QTY,
A.ACCOUNT,
A.DEPTID,
A.MONETARY_AMOUNT,
B.STORAGE_AREA,
B.STOR_LEVEL_1,
A.DISTRIB_TYPE,
A.ORDER_NO,
A.RECEIVER_ID,
E.RECEIVER_ID,
A.RECV_LN_NBR,
A.JOURNAL_ID,
D.RTV_ID,
D.RTV_LN_NBR,
E.REJECT_ACTION,
E.REJECT_REASON,
E.RMA_ID,
E.RMA_LINE_NBR,
C.SHIP_TO_CUST_ID,
E.RTV_LN_STATUS
FROM PS_TRANSACTION_INV B, PS_CM_ACCTG_LINE A, PS_MSR_HDR_INV C, PS_SHIP_INF_INV D, PS_RTV_LN E
WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT
AND B.INV_ITEM_ID = A.INV_ITEM_ID
AND B.DT_TIMESTAMP = A.DT_TIMESTAMP
AND B.SEQ_NBR = A.SEQ_NBR
AND A.BUSINESS_UNIT = '00240'
AND A.TRANSACTION_GROUP = '012'
AND A.ACCOUNT = '2210GRN03'
AND A.JOURNAL_ID BETWEEN 'IN00003801' AND 'IN00003952'
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.ORDER_NO = C.ORDER_NO
AND A.DISTRIB_TYPE = C.DISTRIB_TYPE
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND B.INV_ITEM_ID = D.INV_ITEM_ID
AND A.ORDER_NO = D.ORDER_NO
AND A.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO
AND D.BUSINESS_UNIT = E.BUSINESS_UNIT
AND D.INV_ITEM_ID = E.INV_ITEM_ID
AND D.RTV_ID = E.RTV_ID
AND D.RTV_LN_NBR = E.RTV_LN_NBR

SQL> @EXPLAIN_PLAN

STATEMENT_| ID|PARENT_ID|ACCESS_PLAN |OPTIONS |OBJECT_NAME
----------|------|---------|----------------------------------------|--------------------|--------------------
ALISON | 0| | SELECT STATEMENT | |
ALISON | 1| 0| SORT |UNIQUE |
ALISON | 2| 1| NESTED LOOPS | |
ALISON | 3| 2| NESTED LOOPS | |
ALISON | 4| 3| NESTED LOOPS | |
ALISON | 5| 4| HASH JOIN | |
ALISON | 6| 5| TABLE ACCESS |FULL |PS_RTV_LN
ALISON | 7| 5| TABLE ACCESS |FULL |PS_SHIP_INF_INV
ALISON | 8| 4| TABLE ACCESS |BY INDEX ROWID |PS_TRANSACTION_INV
ALISON | 9| 8| INDEX |RANGE SCAN |PS_TRANSACTION_INV
ALISON | 10| 3| TABLE ACCESS |BY INDEX ROWID |PS_CM_ACCTG_LINE
ALISON | 11| 10| INDEX |RANGE SCAN |PSDCM_ACCTG_LINE
ALISON | 12| 2| TABLE ACCESS |BY INDEX ROWID |PS_MSR_HDR_INV
ALISON | 13| 12| INDEX |RANGE SCAN |PS_MSR_HDR_INV


SQL> SELECT COUNT(*) FROM PS_TRANSACTION_INV;

COUNT(*)
--------------------------------------------------
1147734

SQL> SELECT COUNT(*) FROM PS_CM_ACCTG_LINE;

COUNT(*)
--------------------------------------------------
11647712

SQL> SELECT COUNT(*) FROM PS_MSR_HDR_INV;

COUNT(*)
--------------------------------------------------
5462

SQL> SELECT COUNT(*) FROM PS_SHIP_INF_INV;

COUNT(*)
--------------------------------------------------
67835

SQL> SELECT COUNT(*) FROM PS_RTV_LN;

COUNT(*)
--------------------------------------------------
1717