Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> left outer join with full table scan

left outer join with full table scan

From: <m.fangtao_at_genesis.co.nz>
Date: 11 Jan 2006 12:02:44 -0800
Message-ID: <1137009763.983507.168330@f14g2000cwb.googlegroups.com> 





Hi,


I have a query with 3 full table scans (each table has 360000 rows). I
tried to create indexes,add hints and removed the FTS,but cost was 100
times more than FTS and ran slower. Could anybody help me to tune the
statement? Thanks.



SELECT A.RUN_CNTL_ID, A.BUSINESS_UNIT, B.BUSINESS_UNIT, B.RECEIVER_ID,


B.VENDOR_ID, C.NAME1, D.RECV_LN_NBR, D.INV_ITEM_ID, D.DESCR254_MIXED,
D.QTY_SH_ACCPT, D.RECEIVE_UOM, D.SHIPTO_ID, L.STORAGE_AREA,
L.STOR_LEVEL_1, L.STOR_LEVEL_2, E.PO_ID, E.REQ_ID, E.DELIVERED_TO,
I.COMMENTS_2000, J.DESCRSHORT, J.DESCR, D.BILL_OF_LADING,
D.PACKSLIP_NO, A.OPRID, Q.ATTN_TO,C.SETID,C.VENDOR_ID
FROM PS_RUN_CNTL_PUR A, PS_RECV_HDR B, PS_VENDOR C, PS_RECV_LN_SHIP D,


PS_RECV_LN_DISTRIB E, ((PS_RECV_LN_SHIP G LEFT OUTER JOIN

PS_ITEM_SPEX_PUR H ON H.SETID = G.ITM_SETID AND H.INV_ITEM_ID =


G.INV_ITEM_ID ) LEFT OUTER JOIN PS_SCG_COMMENTS_VW I ON H.OPRID =
I.ASSIGNED_BY_OPRID AND H.RANDOM_CMMT_NBR = I.RANDOM_CMMT_NBR AND
H.COMMENT_ID = I.COMMENT_ID ), PS_SHIPTO_TBL J, (PS_RECV_LN_DISTRIB K


LEFT OUTER JOIN PS_SCG_RECLNITM_VW L ON K.BUSINESS_UNIT =


L.BUSINESS_UNIT AND K.RECEIVER_ID = L.RECEIVER_ID AND K.RECV_LN_NBR =
L.RECV_LN_NBR AND K.RECV_SHIP_SEQ_NBR = L.RECV_SHIP_SEQ_NBR AND
K.DISTRIB_LINE_NUM = L.DISTRIB_LINE_NUM ), ((PS_RECV_LN_DISTRIB M LEFT


OUTER JOIN PS_PO_LINE_DISTRIB N ON M.BUSINESS_UNIT_PO = N.BUSINESS_UNIT

AND M.PO_ID = N.PO_ID AND M.LINE_NBR = N.LINE_NBR AND M.SCHED_NBR =

N.SCHED_NBR AND M.DST_ACCT_TYPE = N.DST_ACCT_TYPE AND

M.PO_DIST_LINE_NUM = N.DISTRIB_LINE_NUM ) LEFT OUTER JOIN PS_REQ_HDR O

ON N.BUSINESS_UNIT = O.BUSINESS_UNIT AND N.REQ_ID = O.REQ_ID ),



(PS_PO_LINE_DISTRIB P LEFT OUTER JOIN PS_PV_REQ_SCH_DTL Q ON


P.BUSINESS_UNIT = Q.BUSINESS_UNIT AND P.REQ_ID = Q.REQ_ID AND

P.REQ_LINE_NBR = Q.LINE_NBR AND P.REQ_SCHED_NBR = Q.SCHED_NBR )

WHERE A.OPRID = 'abc'


AND ( A.RUN_CNTL_ID ='PS_001'


AND A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.RECEIVER_ID = B.RECEIVER_ID
AND B.VENDOR_SETID = C.SETID
AND B.VENDOR_ID = C.VENDOR_ID
AND B.BUSINESS_UNIT = D.BUSINESS_UNIT
AND B.RECEIVER_ID = D.RECEIVER_ID
AND D.RECV_SHIP_STATUS NOT IN ('X','C')
AND D.PRODUCTION_ID = ' '
AND E.BUSINESS_UNIT = D.BUSINESS_UNIT
AND E.RECEIVER_ID = D.RECEIVER_ID
AND E.RECV_LN_NBR = D.RECV_LN_NBR
AND E.RECV_SHIP_SEQ_NBR = D.RECV_SHIP_SEQ_NBR
AND E.DISTRIB_LINE_NUM = (SELECT MIN( F.DISTRIB_LINE_NUM)


FROM PS_RECV_LN_DISTRIB F



WHERE F.BUSINESS_UNIT = E.BUSINESS_UNIT


AND F.RECEIVER_ID = E.RECEIVER_ID
AND F.RECV_LN_NBR = E.RECV_LN_NBR
AND F.RECV_SHIP_SEQ_NBR = E.RECV_SHIP_SEQ_NBR)
AND E.BUSINESS_UNIT = G.BUSINESS_UNIT
AND E.RECEIVER_ID = G.RECEIVER_ID
AND E.RECV_LN_NBR = G.RECV_LN_NBR
AND E.RECV_SHIP_SEQ_NBR = G.RECV_SHIP_SEQ_NBR
AND B.SETID = J.SETID
AND D.SHIPTO_ID = J.SHIPTO_ID
AND J.EFFDT =




(SELECT MAX(J_ED.EFFDT) FROM PS_SHIPTO_TBL J_ED


WHERE J.SETID = J_ED.SETID



AND J.SHIPTO_ID = J_ED.SHIPTO_ID



AND J_ED.EFFDT <= SYSDATE)

AND E.BUSINESS_UNIT = K.BUSINESS_UNIT
AND E.RECEIVER_ID = K.RECEIVER_ID
AND E.RECV_LN_NBR = K.RECV_LN_NBR
AND E.RECV_SHIP_SEQ_NBR = K.RECV_SHIP_SEQ_NBR
AND E.DISTRIB_LINE_NUM = K.DISTRIB_LINE_NUM
AND K.BUSINESS_UNIT = M.BUSINESS_UNIT
AND K.RECEIVER_ID = M.RECEIVER_ID
AND K.RECV_LN_NBR = M.RECV_LN_NBR
AND K.RECV_SHIP_SEQ_NBR = M.RECV_SHIP_SEQ_NBR
AND K.DISTRIB_LINE_NUM = M.DISTRIB_LINE_NUM
AND E.BUSINESS_UNIT = P.BUSINESS_UNIT
AND E.PO_ID = P.PO_ID
AND E.LINE_NBR = P.LINE_NBR
AND E.SCHED_NBR = P.SCHED_NBR )




ORDER BY 12, 7



Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop



SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	3279


  SORT ORDER BY		1  	551  	3279
    NESTED LOOPS OUTER		1  	551  	3277
      HASH JOIN		1  	536  	3277
        NESTED LOOPS OUTER		1  	499  	1208
          NESTED LOOPS		1  	469  	1206
            NESTED LOOPS OUTER		1  	447  	1205

              NESTED LOOPS		1  	415  	1203
                NESTED LOOPS OUTER		1  	381  	1200

                  HASH JOIN		1  	360  	1198
                    TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_RECV_LN_DISTRIB	1  	48  	3
                      NESTED LOOPS		1  	323  	14

                        NESTED LOOPS		1  	275  	11

                          NESTED LOOPS		1  	219  	9

                            NESTED LOOPS		1  	125  	6

                              NESTED LOOPS		1  	88  	5

                                TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_RUN_CNTL_PUR	1  	53  	3
                                  INDEX UNIQUE
SCAN	SYSADM.PS_RUN_CNTL_PUR	1  	 	2
                                TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_RECV_HDR	1  	35  	2
                                  INDEX UNIQUE
SCAN	SYSADM.PS_RECV_HDR	1  	 	1
                              TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_VENDOR	1  	37  	1
                                INDEX UNIQUE SCAN	SYSADM.PS_VENDOR	1

                            TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_RECV_LN_SHIP	1  	94  	3
                              INDEX RANGE SCAN	SYSADM.PS_RECV_LN_SHIP	1
 	 	2
                          TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_SHIPTO_TBL	1  	56  	2
                            INDEX RANGE SCAN	SYSADM.PS_SHIPTO_TBL	1
	1
                              SORT AGGREGATE		1  	22

                                FIRST ROW		1  	22  	2

                                  INDEX RANGE SCAN


(MIN/MAX)	SYSADM.PS_SHIPTO_TBL	471  	 	2

                        INDEX RANGE SCAN	SYSADM.PS_RECV_LN_DISTRIB	1
	2
                          SORT AGGREGATE		1  	22

                            FIRST ROW		1  	22  	3

                              INDEX RANGE SCAN


(MIN/MAX)	SYSADM.PS_RECV_LN_DISTRIB	362 K	 	3


                    VIEW		361 K	12 M	1182
                      HASH JOIN OUTER		361 K	28 M	1182

                        TABLE ACCESS FULL	SYSADM.PS_RECV_LN_SHIP	361
K	14 M	884
                        INDEX FULL SCAN	SYSADM.PS_ITEM_SPEX_PUR	81  	3
K	1
                  TABLE ACCESS BY INDEX ROWID	SYSADM.PS_COMMENTS_TBL	1
	21  	2
                    INDEX UNIQUE SCAN	SYSADM.PS_COMMENTS_TBL	1  	 	1

                TABLE ACCESS BY INDEX ROWID	SYSADM.PS_PO_LINE_DISTRIB	1
 	34  	3
                  INDEX RANGE SCAN	SYSADM.PS_PO_LINE_DISTRIB	1  	 	2

              TABLE ACCESS BY INDEX ROWID	SYSADM.PS_PV_REQ_SCH_DTL	1
	32  	2
                INDEX UNIQUE SCAN	SYSADM.PS_PV_REQ_SCH_DTL	1  	 	1

            INDEX UNIQUE SCAN	SYSADM.PS_RECV_LN_DISTRIB	1  	22  	1

          VIEW PUSHED PREDICATE	SYSADM.PS_SCG_RECLNITM_VW	1  	30  	2

            TABLE ACCESS BY INDEX ROWID	SYSADM.PS_DEFAULT_LOC_INV	1
	31  	3
              NESTED LOOPS		1  	95  	9
                NESTED LOOPS		1  	64  	6
                  TABLE ACCESS BY INDEX ROWID	SYSADM.PS_RECV_LN_SHIP	1
	37  	3
                    INDEX UNIQUE SCAN	SYSADM.PS_RECV_LN_SHIP	1  	 	2

                  TABLE ACCESS BY INDEX
ROWID	SYSADM.PS_RECV_LN_DISTRIB	1  	27  	3
                    INDEX RANGE SCAN	SYSADM.PS_RECV_LN_DISTRIB	1  	 	2

                INDEX RANGE SCAN	SYSADM.PS_DEFAULT_LOC_INV	1  	 	2

        VIEW		362 K	12 M	2067
          HASH JOIN OUTER		362 K	28 M	2067
            TABLE ACCESS FULL	SYSADM.PS_RECV_LN_DISTRIB	362 K	16 M	565

            TABLE ACCESS FULL	SYSADM.PS_PO_LINE_DISTRIB	373 K	12 M	910

      INDEX UNIQUE SCAN	SYSADM.PS_REQ_HDR	1  	15
-----------------------------------------------------------------------------


Received on Wed Jan 11 2006 - 14:02:44 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US