Rewrite the query to avoid TS scan

kanha · New User Joined: 05 Nov 2005 Posts: 28

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

singhju · New User Joined: 01 Dec 2010 Posts: 25 Location: Gurgaon

Hi,

Can you please show us the existing indexes and order of columns in those indexes.

dbzTHEdinosauer · Posted: Wed Jul 06, 2011 4:38 pm

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

kanha · New User Joined: 05 Nov 2005 Posts: 28

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

GuyC · Posted: Wed Jul 06, 2011 5:44 pm

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.

kanha · New User Joined: 05 Nov 2005 Posts: 28

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?

kanha · New User Joined: 05 Nov 2005 Posts: 28

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.

Marso · Posted: Sun Jul 10, 2011 8:53 pm

GuyC · Posted: Tue Jul 12, 2011 11:19 am

my guess is that statistics aren't run or DB2 thinks the table is very small.