Which Operation of the Complex SQL is doing full table scan

9 views
Skip to first unread message

Marcus

unread,
Mar 24, 2005, 9:57:01 PM3/24/05
to
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

Reply all
Reply to author
Forward
0 new messages