|
View previous topic :: View next topic
|
| Author |
Message |
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
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 |
|
 |
abhishekmdwivedi
New User
.jpg)
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
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 |
|
|
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
| 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 |
|
|
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
| there was a spelling mistake in the subject line. Regretted |
|
| Back to top |
|
|
abhishekmdwivedi
New User
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
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 |
|
|
abhishekmdwivedi
New User
Joined: 22 Aug 2006
Posts: 95
Location: india
|
|
|
|
And ya we as technical prodigy do not look for spelling mistakes and hence do not regret.  It's a part of our routines. |
|
| Back to top |
|
|
abhishek mitra
New User
Joined: 24 Feb 2008
Posts: 48
Location: Kolkata
|
|
|
|
| abhishekmdwivedi wrote: |
| And ya we as technical prodigy do not look for spelling mistakes and hence do not regret. It's a part of our routines. |
Thanks a lot  I got the information I was looking for. |
|
| Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| 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 |
|
|
skkp2006
New User
Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India
|
|
| Back to top |
|
|
ravi17s
Warnings : 1
New User
Joined: 15 Aug 2003
Posts: 57
|
|
|
|
| Its always advisable to code the most Restrictive predicates first. |
|
| Back to top |
|
|
|
|
