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)>T O_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

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:

On Wed, 30 Mar 2005 09:45:47 -0500, Scott Mattes wrote:

Few will respond because this is NOT the place to ask. See

http://groups.google.ca/groups?hl=en...bases. oracle