Can this self join be replaced with another efficient query?

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

Hi,

I have below query which is taking more time to execute even though proper indexes exist.

SELECT A.NUM
INTO : WS-NUM
FROM TABLE1 A, TABLE1 B
WHERE B.NUM = :WS-CNT
AND A.NUM LIKE = '04%'
AND A.FIELD1 = B.FIELD1
AND A.FIELD2 = B.FIELD2
AND A.FIELD3 = B.FIELD3

ONECLUSTERING INDEX EXISTS ON FIELD3, FIELD1, FIELD2 IN ORDER. LET ME KNOW, IF ANYTHING MORE IS REQUIRED.

dbzTHEdinosauer · Posted: Mon May 16, 2011 9:55 am

you are only SELECTing the value of a column
that you already know - = ws-cnt and like 04%.

since you are not SELECTing any other column values,
would you not be better off with an EXISTS?

what are you actually trying to determine?

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

I'm trying to tune the query to run faster. TYhis query takes most CPU time in a job that runs 420 times a month.

dbzTHEdinosauer · Posted: Mon May 16, 2011 1:19 pm

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

There are 2 values of NUM field in the table for same field1, field2, field3. I have one value & I'm trying to fetch other value of NUM from the table whose first 2 characters are '04'.

Hope it is clear now.

dbzTHEdinosauer · Posted: Mon May 16, 2011 7:27 pm

GuyC · Posted: Tue May 17, 2011 2:22 pm

Is this index on TableA or on TableB or on both ?
is it Unique ?
Is there an index on TableA that begins with A.NUM ?

The confusion of Dino is probably because the query has a syntax error :
A.NUM LIKE = '04%'
the = sign shouldn't be there

dbzTHEdinosauer · Posted: Tue May 17, 2011 3:07 pm

I was confused because he is doing a 'join on the same table'.

there are not two tables, there is only one table:

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

The = in LIKE clause is a typo. Sorry for that.

Essentialy it is one table. That's why I said "Self jopin". There are 2 values of NUM field in the table. One starts with '01' & another starts with '04'. For both values of NUM, FIELD1, FIELD2, FIELD3 is same.

The qyery identifies the NUM field value that starts with '04' given another value of NUM in WS-CNT.(that starts with '01')

There are 2 indexes. one on NUM(Unique).
Another on FIELD1,FIELD2, FIELD3. (Clustered)

Another doubt is why the INDEX ONLY column in the EXPLAIN is still N as we are accessing all the columns those are present in index.

Let me know, if still it is not clear.

pecram24 · New User Joined: 12 Oct 2009 Posts: 35 Location: Chennai

Hi,

Sorry for asking a question rather answering...

From the query that have given,

SELECT A.NUM
INTO : WS-NUM
FROM TABLE1 A, TABLE1 B
WHERE B.NUM = :WS-CNT
AND A.NUM LIKE = '04%'
AND A.FIELD1 = B.FIELD1
AND A.FIELD2 = B.FIELD2
AND A.FIELD3 = B.FIELD3

say for example if the value of WS-CNT starts with 01.. and u r trying to fetch the values from the above query , you wil get the value that starts with 04....
if the value of WS-CNT starts with 04 then also you 'll get value that starts with 04..
so the value that u r getting is same i.e. the value starts with 04 irrespective to the value of WS-CNT. then why u r having this query.. ?
and the value of FIELD1,FIELD2,FIELD3 are same for the both the values of num ( the one taht starts with 01 and the starts with 04) then what's the need to compare the values in the query ??

GuyC · Posted: Tue May 17, 2011 6:24 pm

a logical access path would be :
- 1 fetch-Ix index1 : B.NUM = :WS-CNT (not Ix-only)
- Matching Ix-scan index2 matching columns 3 (not Ix-only )

It is rather obvious why it is not index only:
Or it uses index with NUM and then it needs to find field1..3
or it uses index with field1..3 and then it needs to find NUM

The smart thing would be to expand the second index to : FIELD1,FIELD2, FIELD3, NUM
then a logical access path would be :
- 1 fetch-Ix index1 : B.NUM = :WS-CNT (not Ix-only)
- Matching Ix-scan index2 matching columns 4 Ix-only

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

pecram24: The value of NUM that starts with '01' comes from another source. We use that to find out the value which starts with '04'. FIELD1, FIELD2, FIELD3 is included in the query because they are same for both values of NUM.

GuyC: I'm not quite clear on your explanation of access paths.
I understand that we can add NUM to INDEX2 but otherwise also it should have INDEX ONLY = N as all the data required is in indexes.

What is better from performance perspective.
1. INDEX ON FIELD1, FIELD2, FIELD3, NUM
2. INDEX ON FIELD1, FIELD2, FIELD3 INCLUDE COLUMN NUM.

GuyC · Posted: Tue May 17, 2011 8:05 pm

1) if NUM is not in index2 then how would db2 resolve "b.NUM like '04%'" ?
(plz don't say via Index1, because if it would use index 1 then how wold DB2 resolve a.FIELD1 = B.FIELD1)

2)Since FIELD1, FIELD2, FIELD3 is not unique, your last question is invalid.
Besides: are you on DB2 10 ? include columns is a v10 feature.

But even if it was possible :
suppose : for each combination of FIELD1..3 there are 100 NUMS : 01% to 99%
when using INDEX ON FIELD1, FIELD2, FIELD3, NUM : it willl only read 1 : namely 04%, otherwise it would read and evaluate all 100 rows.

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

Thanks GuyC.

Besides including NUM in Index2, is there any way we can rewrite the query to run faster? I suppose not but still look for some expert advice.