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.TRANSACTIO N TXN,
LTH4.OPERATION PP_OPERATION, LTH3.OPERATION APO_OPERATION,
LTH3.PREVOUT_DA TE APO_PREVOUT_DAT E
-- LTH_ENDDATE
, CASE WHEN DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)>TO_DATE('20 05-01-22
08:07:55','YYYY-MM-DD HH24:MI:SS')
THEN NULL
-- LTH_STARTDATE
WHEN DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)<=TO_DATE('2 005-01-05
08:07:55','YYYY-MM-DD HH24:MI:SS')
THEN NULL
ELSE DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)
END LTH_LOAD_DATE
-- LA_ENDDATE
, CASE WHEN LA2.LOAD_DATE>T O_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_N UMBER
, LA2.ATTRIBUTE_V ALUE
, LTH4.OLDQTY1-LTH4.NEWQTY1 UNITCOUNT
--, LTH3.OLDQTY1 LTH2_OLDQTY1
, LA2.SRC_ERASE_D ATE LA_SRC_ERASE_DA TE
, LTH4.HISTORY_DE LETED_FLAG LTH_HISTORY_DEL ETED_FLAG
, LTH3.HISTORY_DE LETED_FLAG LTH2_HISTORY_DE LETED_FLAG
FROM
( select distinct LTH2.LOT, LTH2.OPERATION
from
A12_PROD_0.F_Lo tTxnHist LTH -- PiecePart Lot txn
,A12_PROD_0.F_L otTxnHist LTH2 -- APO Lot txn
,A12_PROD_0.F_L otAttribute 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.TRANSACTIO N='ASSM'
AND LTH2.FROM_TO='F '
-- LTH --> LTH2 JOIN
AND LTH.FROM_TO_LOT =LTH2.LOT
AND LTH.TXN_DATE=LT H2.TXN_DATE
-- LTH --> LA Join
AND LA.LOT = LTH.LOT
) APO_LTS
,A12_PROD_0.F_L otTxnHist LTH3 -- APO Lot txn
,A12_PROD_0.F_L otTxnHist LTH4 -- PiecePart Lot txn
,A12_PROD_0.F_L otAttribute LA2
where
-- APO_LTS --> LTH3
LTH3.Lot=APO_LT S.LOT and LTH3.OPERATION= APO_LTS.OPERATI ON
-- LTH3 --> LTH4 -- find the pieceparts for the Apo lots
AND LTH4.FROM_TO_LO T=LTH3.LOT
AND LTH4.TXN_DATE=L TH3.TXN_DATE
-- LTH4 --> LA2
AND LA2.LOT = LTH4.LOT
-- LTH4 Filters
AND LTH4.TRANSACTIO N='ASSM'
AND LTH4.FROM_TO_LO T IS NOT NULL
AND LTH4.FROM_TO='T '
-- LTH3 Filters
AND LTH3.TRANSACTIO N='ASSM'
AND LTH3.FROM_TO='F '
-- LA2 Filters
AND (LA2.attribute_ value is not NULL
AND LA2.Attribute_V alue not in (' ','N/A'))
ORDER BY LTH3.LOAD_DATE
Explain Plan
=============
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (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_LOTTXNHI ST'
(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_LOTATTRIB UTE'
(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_LOTTXNHIS T' (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_LOTTXNHI ST'
(NON-UNIQUE) (Cost=2 Card=185892)
18 2 TABLE ACCESS (BY INDEX ROWID) OF 'F_LOTATTRIBUTE '
(Cost=4 Card=132115571 Bytes=396346713 0)
19 18 INDEX (RANGE SCAN) OF 'XPKF_LOTATTRIB UTE' (UNIQUE)
(Cost=4 Card=132115571)
thanks
Marc
3  5896 
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.TRANSACTIO N TXN,
LTH4.OPERATION PP_OPERATION, LTH3.OPERATION APO_OPERATION,
LTH3.PREVOUT_DA TE APO_PREVOUT_DAT E
-- LTH_ENDDATE
, CASE WHEN DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)>TO_DATE('20 05-01-22
08:07:55','YYYY-MM-DD HH24:MI:SS')
THEN NULL
-- LTH_STARTDATE
WHEN DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)<=TO_DATE('2 005-01-05
08:07:55','YYYY-MM-DD HH24:MI:SS')
THEN NULL
ELSE DECODE(SIGN(LTH 4.LOAD_DATE-LTH3.LOAD_DATE) ,1,LTH3.LOAD_DA TE,LTH4.LOAD_DA TE)
END LTH_LOAD_DATE
-- LA_ENDDATE
, CASE WHEN LA2.LOAD_DATE>T O_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_N UMBER
, LA2.ATTRIBUTE_V ALUE
, LTH4.OLDQTY1-LTH4.NEWQTY1 UNITCOUNT
--, LTH3.OLDQTY1 LTH2_OLDQTY1
, LA2.SRC_ERASE_D ATE LA_SRC_ERASE_DA TE
, LTH4.HISTORY_DE LETED_FLAG LTH_HISTORY_DEL ETED_FLAG
, LTH3.HISTORY_DE LETED_FLAG LTH2_HISTORY_DE LETED_FLAG
FROM
( select distinct LTH2.LOT, LTH2.OPERATION
from
A12_PROD_0.F_Lo tTxnHist LTH -- PiecePart Lot txn
,A12_PROD_0.F_L otTxnHist LTH2 -- APO Lot txn
,A12_PROD_0.F_L otAttribute 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.TRANSACTIO N='ASSM'
AND LTH2.FROM_TO='F '
-- LTH --> LTH2 JOIN
AND LTH.FROM_TO_LOT =LTH2.LOT
AND LTH.TXN_DATE=LT H2.TXN_DATE
-- LTH --> LA Join
AND LA.LOT = LTH.LOT
) APO_LTS
,A12_PROD_0.F_L otTxnHist LTH3 -- APO Lot txn
,A12_PROD_0.F_L otTxnHist LTH4 -- PiecePart Lot txn
,A12_PROD_0.F_L otAttribute LA2
where
-- APO_LTS --> LTH3
LTH3.Lot=APO_LT S.LOT and LTH3.OPERATION= APO_LTS.OPERATI ON
-- LTH3 --> LTH4 -- find the pieceparts for the Apo lots
AND LTH4.FROM_TO_LO T=LTH3.LOT
AND LTH4.TXN_DATE=L TH3.TXN_DATE
-- LTH4 --> LA2
AND LA2.LOT = LTH4.LOT
-- LTH4 Filters
AND LTH4.TRANSACTIO N='ASSM'
AND LTH4.FROM_TO_LO T IS NOT NULL
AND LTH4.FROM_TO='T '
-- LTH3 Filters
AND LTH3.TRANSACTIO N='ASSM'
AND LTH3.FROM_TO='F '
-- LA2 Filters
AND (LA2.attribute_ value is not NULL
AND LA2.Attribute_V alue not in (' ','N/A'))
ORDER BY LTH3.LOAD_DATE
Explain Plan
=============
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOS E (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_LOTTXNHI ST'
(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_LOTATTRIB UTE'
(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_LOTTXNHIS T' (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_LOTTXNHI ST'
(NON-UNIQUE) (Cost=2 Card=185892)
18 2 TABLE ACCESS (BY INDEX ROWID) OF 'F_LOTATTRIBUTE '
(Cost=4 Card=132115571 Bytes=396346713 0)
19 18 INDEX (RANGE SCAN) OF 'XPKF_LOTATTRIB UTE' (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 just have two tables, PARENT and
CHILD.
PARENT(
pkey int primary key,
|
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 into itself. This is so that
people can duplicate a report definition and then edit.. Basically i store
the XMLDATA property of the OWC in a SQL table and have a couple of pages
for finding report defs.. not too complex..
Error Type:...
|
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 loading phase seems quite efficient
and seems to be well parallelized. But the build phase is long and seems to
use only one processor. Very low IO wait occurs during this phase. Is there
something can I do to fasten this process? I guess that if the 4...
|
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 on this table in what order should I hit these
|
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 VBA code that wrote the
results of that away to the db, either creating new records or updating
existing records, whichever was relevant. This may also include deleting
records.
Now I generally do this by opening a recordset on the source data...
| | |
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 column
case 2: There is a compound key on 1 columns col1, col2
case 3: No primary key in the table (Just for my education)
|
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 ict.t_t_id IN ( 3725 )
and res.rr_rr_id = rr.rr_id
AND rr.rt_rt_id = -1
and rr.ict_ict_id = ict.ict_id
|
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 variables declared in SP.
select * from table where pred1 and pred2 and pred3 and variable1
='number1 and variable2 =number2;
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
