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
 

 

Difference Betwwen Two Explains

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

New User


Joined: 25 Apr 2009
Posts: 20
Location: Pune

PostPosted: Tue Aug 18, 2009 12:28 pm    Post subject: Difference Betwwen Two Explains
Reply with quote

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

Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Aug 18, 2009 1:18 pm    Post subject:
Reply with quote

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
View user's profile Send private message
d_pansare

New User


Joined: 25 Apr 2009
Posts: 20
Location: Pune

PostPosted: Tue Aug 18, 2009 1:25 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Aug 18, 2009 1:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
d_pansare

New User


Joined: 25 Apr 2009
Posts: 20
Location: Pune

PostPosted: Tue Aug 18, 2009 1:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Aug 18, 2009 1:36 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 18, 2009 1:41 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Tue Aug 18, 2009 1:52 pm    Post subject:
Reply with quote

Ya you are right
Back to top
View user's profile Send private message
d_pansare

New User


Joined: 25 Apr 2009
Posts: 20
Location: Pune

PostPosted: Tue Aug 18, 2009 1:54 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 18, 2009 1:58 pm    Post subject:
Reply with quote

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
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 Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm
No new posts Difference Between CICS WebSupport an... sachinji84 CICS 1 Mon Mar 07, 2016 7:25 pm
No new posts Difference between -180 and -181 with... deepak_shrivastava DB2 4 Wed Jan 06, 2016 10:00 am


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