473,698 Members | 2,751 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which Operation of the Complex SQL is doing full table scan

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
Jul 19 '05 #1
3 5893
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

Jul 19 '05 #2
On Wed, 30 Mar 2005 09:45:47 -0500, Scott Mattes wrote:

I purposely waited, but since no one has responded I will.


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

http://groups.google.ca/groups?hl=en...tabases.oracle
Jul 19 '05 #3
On Wed, 30 Mar 2005 09:45:47 -0500, Scott Mattes wrote:
>
I purposely waited, but since no one has responded I will.
Few will respond because this is NOT the place to ask. See

http://groups.google.ca/groups?hl=en...tabases.oracle
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
15537
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,
1
655
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:...
4
4373
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...
1
1883
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
34
10834
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...
2
6369
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)
1
3877
by: hastha23 | last post by:
Hi, What is explain plan? what is full table scan? Anybody .. Regards, Hastha23
1
2722
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
4
3881
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;
0
8674
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9026
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8893
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7723
agi2029
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...
1
6518
isladogs
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...
0
5860
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();...
0
4366
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...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2328
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.