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 between ORDER BY and MAX function.

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

New User


Joined: 21 Oct 2009
Posts: 6
Location: Pune, India

PostPosted: Thu Oct 22, 2009 3:14 pm    Post subject: Difference between ORDER BY and MAX function.
Reply with quote

Hi,

My requirement is to fetch the last updated record from a table. I'm using a singleton select. Firstly, i used ORDER BY with FETCH FIRST 1 ROW ONLY and this worked fine. But, as it was increasing CPU consumption and taking longer time, i used MAX function to fetch the record with the latest TIMESTAMP and this too worked fine and took lesser time to run i.e. 45-50% less time than using ORDER BY.

My question is why ORDER BY takes such a longer time than MAX function? can anyone please explain me in a bit detail? I'm new to DB2.
Back to top
View user's profile Send private message

arvind.m

Active User


Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad

PostPosted: Thu Oct 22, 2009 6:42 pm    Post subject:
Reply with quote

Nidhi, a question for you

Is the records fetched by method 1 (ORDER BY with FETCH FIRST 1 ROW ONLY) and method 2 (used MAX function to fetch the record with the latest TIMESTAMP) are same???
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Oct 22, 2009 7:34 pm    Post subject:
Reply with quote

one of these days people may just learn to use proper terminology.

records have nothing to do with DB2 tables - the term is ROWS.

with the ORDER BY and FETCH FIRST,
your result table contains all columns in the select clause,
delimited by your WHERE clause which are sorted.

whereas the result table for the MAX function contains 1 row.

so, you could say the MAX function saves you a sort.
if the table is large, that would be a significant resource cost.
Back to top
View user's profile Send private message
Nidhi_P

New User


Joined: 21 Oct 2009
Posts: 6
Location: Pune, India

PostPosted: Fri Oct 23, 2009 10:36 am    Post subject:
Reply with quote

Quote:
Is the records fetched by method 1 (ORDER BY with FETCH FIRST 1 ROW ONLY) and method 2 (used MAX function to fetch the record with the latest TIMESTAMP) are same???

Yes, both the methods give the same result.

Quote:
records have nothing to do with DB2 tables - the term is ROWS.

Dick, I meant to say the latest record from a set of resulted rows. I'll be more careful with the terms i use.

I'm confused how exactly the MAX works.
1. Does it perform a scan of all the resulted rows from WHERE clause? Does it use forward Scan or reverse scan?
2. The column I'm performing MAX on (TIMESTAMP) is the last column in the INDEX. Does position matter? If the column is the only column in the INDEX or the first column in INDEX, no sorting will happen. But, if not, then will there be any sorting? Please help me to understand how this search actually works.


I referred many previous posts on MAX function and also referred IBM manuals, all i got was just a basic understanding of MAX. Please help.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Oct 23, 2009 4:43 pm    Post subject:
Reply with quote

Quote:
Dick, I meant to say the latest record from a set of resulted rows. I'll be more careful with the terms i use.


yet you miss-used the term record, once again.

the point about terminology
(and it is prevalent in this forum - miss-use of the word JCL is another example)
is that you can not find anything in 'searches' about apples if your search key is oranges.

using the general term latest is an error, everything depends on ORDER.

Now, back to your question.

How a MAX function 'technically works' is something that I don't know or care about.
I do know that SQL performance is improved when DB2 is not forced to do table scans.
Lesson here is that high impact SQL should be based around good indexing (normally).

Ignoring the effect of the WHERE clause(s):
A MAX function will build a result table based on the results of interrogating either indexes or table scans
(the direction of the scan? I have no idea)
- the result table will be comprised of a row containing the MAX value -

when you ORDER BY, db2 will build a result table based on all rows that qualify (from the where clause that I told you to ignore).

thus is can be surmised that the time necessary for db2 to create/build
a result table of 1 row
would be quicker than a result table of more than one row.

analyzing the plan_table entry generated from an EXPLAIN of the sql
will tell you how db2 has decided to generate the result table.

Obviously, if the timestamp is the primary column of an index,
the search is rather easy.
The converse is also obvious:
If the timestamp column is not the primary column of an index, a scan of the index or table is required.
The decision of which to scan will be made by DB2

(the reason can be explained by somebody with intimate knowledge of DB2 code
whom you are not going to find in a forum like this.
More likely scanning a doctorial thesis)

and (again) the plan_table will indicate the decision that was made by DB2 at the time of the EXPLAIN.
Back to top
View user's profile Send private message
Nidhi_P

New User


Joined: 21 Oct 2009
Posts: 6
Location: Pune, India

PostPosted: Mon Oct 26, 2009 5:14 pm    Post subject:
Reply with quote

Quote:
yet you miss-used the term record, once again.

Oops! I did it again... icon_sad.gif

I checked the PLAN_TABLE. It indicates IndexOnly Access. I also learnt that the column i'm using MAX on is a low order column in INDEX and hence, it needs to scan the entire Index to find the MAX value. But, anyways it's better than the table scan.

Thanks Dick, EXPLAIN helped me to understand the query performance. As this is the first time i'm working with DB2, i was not aware of EXPLAIN, PLAN_TABLE etc.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Oct 26, 2009 5:30 pm    Post subject:
Reply with quote

EXPLAIN, PLAN_TABLE
are the third tools in your arsenal in SQL problem solving;
first is SQL Ref manual for syntax;
second is the error code manual.

The IBM - DB2 Product Family Library
is a good starting place to find manuals and other links to DB2 documentation.

this website (Download button at top of this page)
provides a slick little thing called Mainframe Abend Assist.
I suggest downloading the free version.
probably requires coordination with your PC security folks,
but it is worth the bs.
Only thing better is Quick Ref, which costs.
Back to top
View user's profile Send private message
Nidhi_P

New User


Joined: 21 Oct 2009
Posts: 6
Location: Pune, India

PostPosted: Mon Oct 26, 2009 5:42 pm    Post subject: Reply to: Difference between ORDER BY and MAX function.
Reply with quote

Yes, I have the Abend assist installed in my PC. Thanks for providing the link to DB2 manuals and other stuffs, Dick.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Oct 26, 2009 7:16 pm    Post subject:
Reply with quote

Nidhi,

also, congratulations on using EXPLAIN.
you are one of the few who managed to do this on your own (at least without another question on the forum).
Back to top
View user's profile Send private message
Nidhi_P

New User


Joined: 21 Oct 2009
Posts: 6
Location: Pune, India

PostPosted: Mon Oct 26, 2009 10:04 pm    Post subject: Reply to: Difference between ORDER BY and MAX function.
Reply with quote

Yes, i managed to use EXPLAIN by myself but I must say it's because you guys have explained it very nicely in previous posts. Thanks to you guys for putting efforts and time.
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm


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