Re: Which Operation of the Complex SQL is doing full table scan

I purposely waited, but since no one has responded I will.

You could decompose this complex query to the simplest, use Explain Plan on that and then build the query back up step by step until you find the problem area.

Marcus wrote:
> 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 Wed Mar 30 2005 - 16:45:47 CEST