Home -> Usenet -> comp.databases.oracle -> Which Operation of the Complex SQL is doing full table scan

Which Operation of the Complex SQL is doing full table scan

From: Marcus <marcus_chn_at_yahoo.com>
Date: 24 Mar 2005 18:57:01 -0800
Message-ID: <a9eeace0.0503241857.36e3adb1_at_posting.google.com> 





Hi


I have a very complex sql query and a explain plan. I found there is a
full table scan in ID=9

   9    8                   TABLE ACCESS (FULL) OF 'F_LOTTXNHIST'




(Cost=84573 Card=185892 Bytes=7063896)





How can I correlate which part of the SQL statement is running on full
table scan. Please see below for the code and explain plan



SQL Code




SELECT



 LTH4.LOT PP_LOT, LTH3.LOT APO_LOT, LTH4.TRANSACTION TXN,

LTH4.OPERATION PP_OPERATION, LTH3.OPERATION APO_OPERATION,

LTH3.PREVOUT_DATE APO_PREVOUT_DATE



--            LTH_ENDDATE



, CASE WHEN DECODE(SIGN(LTH4.LOAD_DATE-LTH3.LOAD_DATE),1,LTH3.LOAD_DATE,LTH4.LOAD_DATE)>TO_DATE('2005-01-22

08:07:55','YYYY-MM-DD HH24:MI:SS')


        THEN NULL
--            LTH_STARTDATE




 WHEN DECODE(SIGN(LTH4.LOAD_DATE-LTH3.LOAD_DATE),1,LTH3.LOAD_DATE,LTH4.LOAD_DATE)<=TO_DATE('2005-01-05
08:07:55','YYYY-MM-DD HH24:MI:SS')


      THEN NULL
     ELSE  DECODE(SIGN(LTH4.LOAD_DATE-LTH3.LOAD_DATE),1,LTH3.LOAD_DATE,LTH4.LOAD_DATE)
     END  LTH_LOAD_DATE
--     LA_ENDDATE




, CASE  WHEN LA2.LOAD_DATE>TO_DATE('2005-01-22 06:47:41','YYYY-MM-DD

HH24:MI:SS')

        THEN NULL
--     LA_STARTDATE




 WHEN LA2.LOAD_DATE<=TO_DATE('2005-01-05 06:47:41','YYYY-MM-DD
HH24:MI:SS')

     THEN NULL
     ELSE  LA2.LOAD_DATE
     END  LA_LOAD_DATE
, LA2.ATTRIBUTE_NUMBER




, LA2.ATTRIBUTE_VALUE



, LTH4.OLDQTY1-LTH4.NEWQTY1 UNITCOUNT



--, LTH3.OLDQTY1 LTH2_OLDQTY1


, LA2.SRC_ERASE_DATE LA_SRC_ERASE_DATE
, LTH4.HISTORY_DELETED_FLAG LTH_HISTORY_DELETED_FLAG
, LTH3.HISTORY_DELETED_FLAG LTH2_HISTORY_DELETED_FLAG


FROM



( select distinct LTH2.LOT, LTH2.OPERATION


 from


 A12_PROD_0.F_LotTxnHist LTH -- PiecePart Lot txn
 ,A12_PROD_0.F_LotTxnHist LTH2  -- APO Lot txn
 ,A12_PROD_0.F_LotAttribute LA


 Where


    
  
  •   DATE RANGE FILTER LTH     LTH_STARTDATE

    (( LTH.LOAD_DATE > TO_DATE('2005-01-05 08:07:55','YYYY-MM-DD

HH24:MI:SS')
  
  •   LTH_ENDDATE

    AND LTH.LOAD_DATE <= TO_DATE('2005-01-22 08:07:55','YYYY-MM-DD
HH24:MI:SS') )
    OR
  
  •   DATE RANGE FILTER LTH2     LTH_STARTDATE

    ( LTH2.LOAD_DATE > TO_DATE('2005-01-05 08:07:55','YYYY-MM-DD

HH24:MI:SS')
  
  •   LTH_ENDDATE

    AND LTH2.LOAD_DATE <= TO_DATE('2005-01-22 08:07:55','YYYY-MM-DD
HH24:MI:SS') )
    OR
  
  •   DATE RANGE FILTER  LA     LA_STARTDATE

    ( LA.LOAD_DATE > TO_DATE('2005-01-05 06:47:41','YYYY-MM-DD

HH24:MI:SS')
  
  •   LA_ENDDATE

    AND LA.LOAD_DATE <= TO_DATE('2005-01-22 06:47:41','YYYY-MM-DD
HH24:MI:SS') ))
  
  •   LTH Filters
    AND LTH.TRANSACTION='ASSM'

    AND LTH.FROM_TO_LOT IS NOT NULL

    AND  LTH.FROM_TO='T'

  
  •   LTH2 Filters
    AND LTH2.TRANSACTION='ASSM'

    AND  LTH2.FROM_TO='F'

  
  •   LTH --> LTH2 JOIN

    AND  LTH.FROM_TO_LOT=LTH2.LOT

    AND  LTH.TXN_DATE=LTH2.TXN_DATE

  
  •   LTH --> LA Join
      AND LA.LOT = LTH.LOT

) APO_LTS

,A12_PROD_0.F_LotTxnHist LTH3 -- APO Lot txn
,A12_PROD_0.F_LotTxnHist LTH4  -- PiecePart Lot txn
,A12_PROD_0.F_LotAttribute LA2
where
-- APO_LTS --> LTH3

LTH3.Lot=APO_LTS.LOT and LTH3.OPERATION=APO_LTS.OPERATION
-- LTH3 --> LTH4 -- find the pieceparts for the Apo lots
AND  LTH4.FROM_TO_LOT=LTH3.LOT

AND  LTH4.TXN_DATE=LTH3.TXN_DATE

-- LTH4 --> LA2

AND LA2.LOT = LTH4.LOT

-- LTH4 Filters
AND LTH4.TRANSACTION='ASSM'

AND LTH4.FROM_TO_LOT IS NOT NULL

AND  LTH4.FROM_TO='T'

-- LTH3 Filters
AND LTH3.TRANSACTION='ASSM'

AND  LTH3.FROM_TO='F'

-- LA2 Filters
AND  (LA2.attribute_value is not NULL
AND  LA2.Attribute_Value not in (' ','N/A'))
ORDER BY LTH3.LOAD_DATE






Explain Plan





Execution Plan




   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=140360 Card=107
Bytes=15194)


   1    0   SORT (ORDER BY) (Cost=140360 Card=107 Bytes=15194)

   2    1     NESTED LOOPS (Cost=140355 Card=107 Bytes=15194)
   3    2       NESTED LOOPS (Cost=140351 Card=1 Bytes=112)
   4    3         NESTED LOOPS (Cost=140350 Card=1 Bytes=59)
   5    4           VIEW (Cost=140348 Card=3 Bytes=33)
   6    5             SORT (UNIQUE) (Cost=140348 Card=3 Bytes=282)
   7    6               NESTED LOOPS (Cost=140345 Card=3 Bytes=282)
   8    7                 NESTED LOOPS (Cost=140341 Card=1 Bytes=76)
   9    8                   TABLE ACCESS (FULL) OF 'F_LOTTXNHIST'


(Cost=84573 Card=185892 Bytes=7063896)


  10    8                   TABLE ACCESS (BY INDEX ROWID) OF
'F_LOTTXNHIST' (Cost=1 Card=185892 Bytes=7063896)
  11   10                     INDEX (RANGE SCAN) OF 'XIE1F_LOTTXNHIST'


(NON-UNIQUE) (Cost=2 Card=185892)


  12    7                 TABLE ACCESS (BY INDEX ROWID) OF
'F_LOTATTRIBUTE' (Cost=4 Card=141055314 Bytes=25389
          95652)

  13   12                   INDEX (RANGE SCAN) OF 'XPKF_LOTATTRIBUTE'


(UNIQUE) (Cost=4 Card=141055314)


  14    4           TABLE ACCESS (BY INDEX ROWID) OF 'F_LOTTXNHIST'


(Cost=1 Card=185892 Bytes=8922816)


  15   14             INDEX (RANGE SCAN) OF 'XPKF_LOTTXNHIST' (UNIQUE)


(Cost=4 Card=185892)


  16    3         TABLE ACCESS (BY INDEX ROWID) OF 'F_LOTTXNHIST'


(Cost=1 Card=185892 Bytes=9852276)


  17   16           INDEX (RANGE SCAN) OF 'XIE1F_LOTTXNHIST'


(NON-UNIQUE) (Cost=2 Card=185892)


  18    2       TABLE ACCESS (BY INDEX ROWID) OF 'F_LOTATTRIBUTE'


(Cost=4 Card=132115571 Bytes=3963467130)


  19   18         INDEX (RANGE SCAN) OF 'XPKF_LOTATTRIBUTE' (UNIQUE)




(Cost=4 Card=132115571)





thanks


Marc
Received on Fri Mar 25 2005 - 03:57:01 CET












Original text of this message