469,329 Members | 1,470 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,329 developers. It's quick & easy.

GROUP BY and SORT

Hello,

DB2 seems to do a SORT before every GROUP BY action. At least if I look
at the query plan (using Visual Explain in the Command Center) of

-- connected to the sample db
SELECT WORKDEPT, SUM(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT

a SORT is performed before GRPBY. I can imagine that it's necessary,
but why?

Greetings, Martin.

Dec 1 '06 #1
1 3050
Ma**********@mailbox.tu-dresden.de wrote:
Hello,

DB2 seems to do a SORT before every GROUP BY action. At least if I look
at the query plan (using Visual Explain in the Command Center) of

-- connected to the sample db
SELECT WORKDEPT, SUM(SALARY)
FROM EMPLOYEE
GROUP BY WORKDEPT

a SORT is performed before GRPBY. I can imagine that it's necessary,
but why?
The rows in your above example could be read in any arbitrary order
(internally). So calculating the SUM for each group that way would mean
that DB2 has to calculate "n" sums in parallel where "n" is the number of
groups (= #workdept). If you do a sort first (by workdept), you can
pipeline things: calculate sum for first group, return result, then
calculate sum for 2nd group, return result, sum for 3rd group, return
result, ... One advantage is that memory consumption for the summation is
known up front and fixed.

Note that you _must not_ rely on GROUP BY doing a SORT internally. A sort
is surely just one option to implement a grouping. For example, if the
DBMS knows up front that you have only 2 workdepts, it may very well pick
the parallel approach. In other words, the _only_ thing that guarantees a
specific order of rows in a result set is an ORDER BY clause. Period.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Dec 1 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Mike Nolan | last post: by
2 posts views Thread by adrian.chandler | last post: by
1 post views Thread by Jamie Moore | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.