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
 

 

Can this self join be replaced with another efficient query?

 
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: Sun May 15, 2011 7:52 pm    Post subject: Can this self join be replaced with another efficient query?
Reply with quote

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.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon May 16, 2011 9:55 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Mon May 16, 2011 1:12 pm    Post subject: Reply to: Can this self join be replaced with another effici
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon May 16, 2011 1:19 pm    Post subject:
Reply with quote

Quote:
I'm trying to tune the query to run faster


that does not answer my question.

as i see it, the query is not written very well, for the reasons I gave.

again, what result are you attempting to receive.

as far as I can see,
you will only receive values = ws-cnt, so why select num?
it can only be equal to ws-cnt.
the reason that it is slow? probably due to the like predicate in the where clause,
that does not make any sense.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Mon May 16, 2011 6:56 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon May 16, 2011 7:27 pm    Post subject:
Reply with quote

Quote:
Hope it is clear now


not really, but someone else can help you,
i am going home.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 17, 2011 2:22 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue May 17, 2011 3:07 pm    Post subject:
Reply with quote

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

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

FROM TABLE1 A, TABLE1 B


that would mean there is only one column num:

Quote:

WHERE B.NUM = :WS-CNT
AND A.NUM LIKE = '04%'


and as GuyC said: LIKE = is BS.

you have only one table, and you have two WHERE clauses
on the same column.


He can't be bothered to provide the SQL in question,
I can't be bothered trying to extract the correct details
to answer his question about a query of which I have no knowledge.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Tue May 17, 2011 5:26 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
pecram24

New User


Joined: 12 Oct 2009
Posts: 35
Location: Chennai

PostPosted: Tue May 17, 2011 6:15 pm    Post subject: Reply to: Can this self join be replaced with another effici
Reply with quote

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 ??
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 17, 2011 6:24 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Tue May 17, 2011 6:57 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue May 17, 2011 8:05 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
kanha

New User


Joined: 05 Nov 2005
Posts: 28

PostPosted: Wed May 18, 2011 11:49 am    Post subject: Reply to: Can this self join be replaced with another effici
Reply with quote

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.
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Efficient sorting chandracdac DFSORT/ICETOOL 5 Sat Oct 22, 2016 3:23 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm


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