By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

Does DB2 sort an empty resultset?

P: n/a
Hello,

If the explain shows that DB2 needs to sort the result of a cursor,
does that always happen? i.e. if the resultset of the cursor is empty,
does the sort have any overhead? It appears that, majority of the time
is spent in allocation and deallocation of the temporary file to hold
the data for the sort. Do these activities still take place for an
empty cursor?

Also, DB2 manual appears to say that only RID sorts are done in
memory, does this mean all data sorts, however small, always require
data to be materialized in a temp. file?

TIA

P. Adhia

PS: these questions relate to DB2 Z/OS 7.1, but I'd be interested in
knowing how UDB for LUW optimizes.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Have no idea about Z/os. I only think the behavior of db2 udb for LUW.
Even you got an empty result set, db2 might still run a sort. Since during
the compile phase, db2 optimizer need to figure out the size of result set,
this is one important part for the following access plan choosing. The
result set size is an estimated cardinality, which will depend on the
data/index distribution/statistics information which saved in the system
catalog tables. Only during the runtime, db2 can know exactly how big the
result size.
I don't know what the corresponding part for RID sorts in DB2 UDB on LUW.
Anyway, db2 udb for LUW doesn't encourage to use physical RID. Generally,
even you see db2 create system temporary table for the sort, this doesn't
mean you must get I/O.

"P Adhia" <pa****@yahoo.com> wrote in message
news:32*************************@posting.google.co m...
Hello,

If the explain shows that DB2 needs to sort the result of a cursor,
does that always happen? i.e. if the resultset of the cursor is empty,
does the sort have any overhead? It appears that, majority of the time
is spent in allocation and deallocation of the temporary file to hold
the data for the sort. Do these activities still take place for an
empty cursor?

Also, DB2 manual appears to say that only RID sorts are done in
memory, does this mean all data sorts, however small, always require
data to be materialized in a temp. file?

TIA

P. Adhia

PS: these questions relate to DB2 Z/OS 7.1, but I'd be interested in
knowing how UDB for LUW optimizes.

Nov 12 '05 #2

P: n/a
Answer relates to DB2 for LUW:
Unless Db2 can proove the resultset is empty it has to at least set up
shop for the SORT operation. So there will be soem minimal work to be
done. Whether DB2 allocates the sort memory when the first ro wis found
or earlier I do not know.

DB2 for LUW will always try to sort in memory. If it can't, because the
size was miscalculated or too much memory is needed that's called a sort
overflow. Sort overflows occur to temp first to the bufferpool and then
if all fails to disc.
Your favorite tool (Quest Spotlight, Activity Monitor GUI, event
monitor, snapshot monitor, ...) will tell you whether a query got victim
of a sort overflow.

Cheers
Serge
Nov 12 '05 #3

P: n/a
Thanks Fan Ruo and Serge for your answers. (I knew Z/OS guys don't
hang out in this group ;) ).

Just to give background,

We have an application that checks every few minutes to see if any new
rows got added after it had checked last time. It uses following SQL.

select columns
from table
where added_timestamp > :last_checked
order by added_timestamp

During majority of the checks, cursor will not return any rows. The
table size will be maintained "small" (< 1000 rows). So we are
debating if we need an index on added_timestamp to avoid the sort
(becaue of order by) that shows up in explain.
I don't know what the corresponding part for RID sorts in DB2 UDB on LUW.
Anyway, db2 udb for LUW doesn't encourage to use physical RID.


RID sort on Z/OS refers to DB2 sort required for list prefetch, hybrid
join, multi-index access etc.

Thanks again.
Nov 12 '05 #4

P: n/a
If the cursor comes back empty most of the time then you may get better
performance by checking if there are any rows that meet the criteria.
This would not involve setting up the sort. When rows exist; do the select.

select count(*) from table where .......

From a practical standpoint; a "count(*)" will scan the entire table
bacause there's no index. If the second query is needed; all pages
(small number of rows) will hopefully be in the buffer pool and should
not require additional I/O or read-ahead scanning will be done that
should eliminate I/O waits. If you have an index on the timestamp; then
the "count(*)" should scan the index instead of the data - a faster process.

An index by itself is may or may not avoid the sort. Using dates for
clustering indexes also causes physical space issues because new rows
always add to the end while deletes free up space at the beginning.
Reorgs are needed to maintain reasonable space use; both in the data and
in the index.

Optimization also looks at relative percentage of pages to be processed
when deciding to use an index or scan (again using statistics). At 250
bytes/row (you didn't specify the size) and a 4k page size; the entire
table will occupy around 65 pages. If you have an index on the
timestamp; then using "OPTIMIZE FOR 1 ROWS" in the select statement will
bias the optimizer to using the index. This can also help avoid the
sort. I've seen cases where using an index required two sorts - one on
RIDs from the index to optimize physical retrieval and a second on the
data to meet the SQL statement sequencing!

Phil Sherman

P Adhia wrote:
Thanks Fan Ruo and Serge for your answers. (I knew Z/OS guys don't
hang out in this group ;) ).

Just to give background,

We have an application that checks every few minutes to see if any new
rows got added after it had checked last time. It uses following SQL.

select columns
from table
where added_timestamp > :last_checked
order by added_timestamp

During majority of the checks, cursor will not return any rows. The
table size will be maintained "small" (< 1000 rows). So we are
debating if we need an index on added_timestamp to avoid the sort
(becaue of order by) that shows up in explain.

I don't know what the corresponding part for RID sorts in DB2 UDB on LUW.
Anyway, db2 udb for LUW doesn't encourage to use physical RID.

RID sort on Z/OS refers to DB2 sort required for list prefetch, hybrid
join, multi-index access etc.

Thanks again.


Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.