|
View previous topic :: View next topic
|
| Author |
Message |
d_pansare
New User
Joined: 25 Apr 2009
Posts: 20
Location: Pune
|
|
|
|
Hi
I am working out on tunining a particular query using Explain. While analyzing I came across small query regarding Explain. Below I have given two similar queries. Can you please tell me whether Explain will give different results as far as cost/time required is concerned in case of below queries.
1. Select * from Employee_database where Emp_id = 12345;
2. Select * from Employee_database where emp_id = 111111111;
Thanks
Dipak |
|
| Back to top |
|
 |
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| It will depends on the table structure and the number of records on that particular table. If you provide me with the exact query then I will try to help u in fine tune the query |
|
| Back to top |
|
|
d_pansare
New User
Joined: 25 Apr 2009
Posts: 20
Location: Pune
|
|
|
|
| Actually I am looking for difference between explains on above two queries. I would like to know whether cost shown by Explain will be differerent or same in above case. In short, whether Explain consider the values given in condition or it is independent of vlaue and just takes predicates. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
I think that value will come out to be same if you check it thru explain but when u use the above query in code then time may vary for processing the above two query.
PS. i do not have the acess to explain as of now, I am telling this on the basis of my exp on explain. |
|
| Back to top |
|
|
d_pansare
New User
Joined: 25 Apr 2009
Posts: 20
Location: Pune
|
|
|
|
Yeah.. thanks.. Even I think the actual processing time will be different depending upon the system performance, values to be passed in the condition etc.
In case of above two queries, suppose, we are lookling for records which is not present in the table. Then DB2 will require more time compared to if record is present. I believe while running Explain, these things are not considered as query is not actually executed. I just wanted to confirm this. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| but by chaging this wont tune the program much for the detail anakysis of all the query and then fine tune . If you need some help on fine tuning then let me know |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
In case of above two queries, suppose, we are lookling for records which is not present in the table. Then DB2 will require more time compared to if record is present.
|
Wrong - if your emp_id is the index (depends on table scan or index scan). Both the queries shown above will have the same explain statistics. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| Ya you are right |
|
| Back to top |
|
|
d_pansare
New User
Joined: 25 Apr 2009
Posts: 20
Location: Pune
|
|
|
|
Hi ashimer
Just to confirm you are saying.. that if index is built on emp_id then we will have same statistics.. otherwise explain statistic will be different..
Thanks
Dipak |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Statistics would be different if emp_id is not the index ...but for the above queries it would be the same no matter what is the value of emp_id . |
|
| Back to top |
|
|
|
|
