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  5872 
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
guidance.
The situation is pretty simple. I...
|
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
(insert into select from) from one table back...
|
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 am on
a quadriprocessor Solaris machine. The...
|
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
block 100000
scan 45
If I'm doing a query...
|
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
that resulted in data in temp tables, I would have...
|
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
following scenario
case 1: There is a primary key on a...
|
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, item_claims_trials ict, results res
WHERE...
|
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 ones, but they were not based on
table columns but...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |
