Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Rewrite the query to avoid TS scan

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Wed Jul 06, 2011 3:22 pm    Post subject: Rewrite the query to avoid TS scan
Reply with quote

I have the below query & it is doing TS scan on TABLE2. Can anyone advise me how to avoid TS scan on the table.

Please note that already an index exists on all columns referred in the query.

SELECT T2.CSN_ID AS CSN_ID,
COALESCE(T4.VNDR_STYLE,'NO STYLE') AS VNDR_STYLE,
SUM(T2.UPC_SHIP_QTY) AS ASN_UNITS
FROM TABLE1 T1
INNER JOIN
TABLE2 T2
ON T2.ASN_NBR = T1.ASN_NBR AND
T2.PO_NBR = T1.PO_NBR AND
T2.PO_STR_NBR = T1.PO_STR_NBR
INNER JOIN
TABLE T3
ON T2.CSN_ID = T3.CSN_ID
LEFT OUTER JOIN
TABLE4 T4
ON T2.COMP_UPC = T4.UPC
WHERE
T1.UPD_DT <= CURRENT_TIMESTAMP AND
T1.UPD_DT > '2011-05-24-13.00.18.118159'
GROUP BY T2.CSN_ID,
T4.VNDR_STYLE
WITH UR

Below tricks are tried & no benefit.
1. Swap the position of TABLE1 & TABLE2
2. SWAP the position of join columns between T1 & T2
Back to top
View user's profile Send private message

singhju

New User


Joined: 01 Dec 2010
Posts: 25
Location: Gurgaon

PostPosted: Wed Jul 06, 2011 3:55 pm    Post subject:
Reply with quote

Hi,

Can you please show us the existing indexes and order of columns in those indexes.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Wed Jul 06, 2011 4:38 pm    Post subject:
Reply with quote

in addition, Table scans are the result of:
  • really poor runstats
  • optimizer for its own reasons deciding to TS instead of IS

there are more reasons

and additionally,
when you supply the indexes with columns and order of columns within the index,
also supply the datatype/definition of each column.

of special interest are these two columns:
T2.COMP_UPC
T4.UPC
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Wed Jul 06, 2011 4:40 pm    Post subject:
Reply with quote

COLUMN NAME COLSEQ ORD

ASN_NBR 1 A
PO_NBR 2 A
PO_STR_NBR 3 A
CSN_ID 4 A
VNDR_UPC 5 A
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Jul 06, 2011 5:44 pm    Post subject:
Reply with quote

not enough info has been given.
what is the accesspath ?
Ix-scan on T1 followed by a Tbscan on T2
or
tbscan on T2 followed by an Ixscan on T1

how many rows in T1 , T2 ?
Is T3 needed for existance check? Because you use no fields of it.

What are the available (duplicate/unique) indexes on T1 and on T2 ?

I doubt that in this case by rewritting the query alone you can avoid TS.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Sun Jul 10, 2011 8:24 pm    Post subject: Reply to: Rewrite the query to avoid TS scan
Reply with quote

Access path s Ix-scan on T1 followed by a Tbscan on T2 .

yes T3 is for existence check.

Yes, on the same columns of T1, an index exists. So, it is not checking for the index on table2 & doing a TS scan.

Is there a way so that indexing will occur on both the tables? or indexing on both the tables wont improve the performance in this case?
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Sun Jul 10, 2011 8:31 pm    Post subject:
Reply with quote

Why the indexing on T2 & TS scan on T1 is not happening? This query selects data from T2 & does 2 joins on T2; once with T1 & another with T3.
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Sun Jul 10, 2011 8:53 pm    Post subject: Re: Rewrite the query to avoid TS scan
Reply with quote

kanha wrote:
Can anyone advise me how to avoid TS scan on the table.
The best advice I can give is "talk to your DBA".
But that's just my opinion...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Tue Jul 12, 2011 11:19 am    Post subject:
Reply with quote

my guess is that statistics aren't run or DB2 thinks the table is very small.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts CICS Question RE: Browse, Readnext an... moezbud CICS 7 Sun Mar 05, 2017 5:45 am
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us