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
 

 

Procedence of query evaluation

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

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Wed Mar 05, 2008 11:17 am    Post subject: Procedence of query evaluation
Reply with quote

hi,

Let's say there is a query,

Select * from Table1, T
where T.a= "b"
and T.a1 = "c"
and T.a2 = "d"
;

now while evaluating the "Where" clause will it be done from bottom to top or vice versa? i.e. whether T.a2 will be evaluated first or last? what is the principle behind it? Can anybody give an idea?
Back to top
View user's profile Send private message

abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Mar 05, 2008 11:44 am    Post subject:
Reply with quote

Well abhishek your query is not an sub query(...) it's a simple query with AND conditions and I didn't understand why "evaluating" any of "T.a= "b"
and T.a1 = "c" and T.a2 = "d" " will be of any logic or principle. As this is simple concatenation of three conditions and not a selection of conditions.

Hence there will not be any core principle to evaluate which condition comes first or last. Try some of the DB2 SQL manual from the forums and you will understand the your query.
Back to top
View user's profile Send private message
abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Wed Mar 05, 2008 11:59 am    Post subject:
Reply with quote

abhishekmdwivedi wrote:
Well abhishek your query is not an sub query(...) it's a simple query with AND conditions and I didn't understand why "evaluating" any of "T.a= "b"
and T.a1 = "c" and T.a2 = "d" " will be of any logic or principle. As this is simple concatenation of three conditions and not a selection of conditions.

Hence there will not be any core principle to evaluate which condition comes first or last. Try some of the DB2 SQL manual from the forums and you will understand the your query.


hi,
now my query is related to that. how a where clause is evaluated by the system? bottom to top or vice versa. It should not differ for a simple query or a a query with "join".
the answer to this can help us in the sense that say we have a query where we have reference to other table and also to some constant value as follows
select * from T1, T2
where T1.a=T2.a
and T1.b = "B"

as we know while binding the the query optimizer always finds the shortest path. now if the where clause is evaluated bottom to top then we shall always specify the constant comparison at bottom because that will filter out few rows before getting in the join. so getting lesser time of CPU.
Or in the opposite case we can give the T1.b = "B" at the top.
I think my query is clear this time.
Back to top
View user's profile Send private message
abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Wed Mar 05, 2008 12:19 pm    Post subject: procedure of Query evaluation by system
Reply with quote

there was a spelling mistake in the subject line. Regretted
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Mar 05, 2008 12:43 pm    Post subject:
Reply with quote

Hi Abhishek,

Here's an example for your understanding:

Select b.name,c.marks from tab1 a, tab2 b, tab3 c where
a.rollno = b.rollno and
b.name = c.name and
c.marks > 70 and c. marks < 90;

Now here this query gets materialized by DB2 as:

1) it will fetch all the records from tab3 where marks are between 70 & 90. lets say there are 100 students.

2) above 100 fetch records are then cross checked for the field NAME being present in tab2 and lets say the there are 10 matches.

3) These 10 matched records are again checked for the roll no. match from tab1 which results in only one record.

This is how the DB2 works. And yes it follows the BOTTOM-TOP approach only.

But it is not certain that a constant being checked in the bottom of the query will always generate lesser record for upper condition to check.
This is mere logic that you will have to apply while going for a query by looking at your requirements and needs + the amount of data present in the table.
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Mar 05, 2008 12:44 pm    Post subject:
Reply with quote

And ya we as technical prodigy do not look for spelling mistakes and hence do not regret. icon_biggrin.gif It's a part of our routines.
Back to top
View user's profile Send private message
abhishek mitra

New User


Joined: 24 Feb 2008
Posts: 48
Location: Kolkata

PostPosted: Wed Mar 05, 2008 12:47 pm    Post subject:
Reply with quote

abhishekmdwivedi wrote:
And ya we as technical prodigy do not look for spelling mistakes and hence do not regret. icon_biggrin.gif It's a part of our routines.


Thanks a lot icon_smile.gif I got the information I was looking for.
Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Wed Mar 05, 2008 6:42 pm    Post subject:
Reply with quote

abhishekmdwivedi: You are familiar enough with the DB2 optimizer to say with confidence that it always works bottom-top? I wouldn't be. The optimizer has the ability to drastically alter your query. Are you sure it doesn't look at indexed fields first before doing constants at the bottom level?
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Thu Mar 06, 2008 12:15 pm    Post subject: Reply to: Procedence of query evaluation
Reply with quote

abhishekmdwivedi & abhishek mitra

Maybe the following link can give you better idea....

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_stage1and2predicates.htm
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Mar 06, 2008 3:08 pm    Post subject: Reply to: Procedence of query evaluation
Reply with quote

Its always advisable to code the most Restrictive predicates first.
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
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


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