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
3  5788 
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)>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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: ryan |
last post by:
Hi Everyone,
I'm having trouble convincing myself that Oracle is executing a query
of mine as efficiently as possible, and I'm looking for a little...
|
by: |
last post by:
So, I am getting this really annoying error.. the SQL Statement executes
perfectly in Query Analyzer.. all I am doing is basically duplicating a row...
|
by: Xela |
last post by:
Hi
I am facing the following problem. I load a fact table with around 25
millons lines, and 7 indexes. I load it with 3 million line subsets. I...
|
by: rloef |
last post by:
I have a multi-million row table with three indexes in MySQL-5.0.15.
These indexes have the following number of distinct values:
date 415
...
|
by: Jeff |
last post by:
For years I have been using VBA extensively for updating data to tables
after processing. By this I mean if I had to do some intensive processing...
|
by: db2udbgirl |
last post by:
If I perform a select count(*) from tred.order_delivery query will it
internally perform a full table scan to determine the row count for the...
|
by: hastha23 |
last post by:
Hi,
What is explain plan?
what is full table scan?
Anybody ..
Regards,
Hastha23
|
by: shilpasharma |
last post by:
Hi,
Can anybody let me know how I can optimise following Query.
Select * from reports
where
( exists ( SELECT 1 FROM results_required rr,...
|
by: Arun Srinivasan |
last post by:
Hi
I was using a query previously, that was efficient
select * from table where pred1 and pred2 and pred3;
Later I was asked to introduce new...
|
by: jalbright99669 |
last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
|
by: Matthew3360 |
last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function.
Here is my code.
...
|
by: Matthew3360 |
last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
|
by: AndyPSV |
last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
|
by: Arjunsri |
last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
|
by: WisdomUfot |
last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
|
by: Matthew3360 |
last post by:
Hi,
I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
|
by: Oralloy |
last post by:
Hello Folks,
I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA.
My problem (spelled failure) is with the...
|
by: BLUEPANDA |
last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...
| |
