MVSFORUMS.com

[jctgf]

hi,

i have a cursor that will select thousands (or millions) of lines. this is the requirement.

i thought i could limit the number of rows read with a fetch 1st (and prevent the aplication of abending due to a timeout), but then i found the following in the db2 manual:

| If both the FETCH FIRST clause and the ORDER BY clause are specified, the
| ordering is performed on the entire result table prior to returning the
| first n rows.

because i'd have to order the lines, i think now that the fetch 1st will not solve my problem.

my question is: how does fetch 1st work if i do NOT have the order by clause? will db2 stop reading/selecting the rows as soon as it reaches the number of lines specified in the fetch 1st clause?

or, will it read/select all the lines that satify the ''where'' clause - without considering the fetch 1st clause - and only when sending the lines back to the cobol program it will limit the number of lines to what i've specified in the fetch 1st statement?

thanks.

[kolusu]

[jctgf]

hey kolusu,
thanks a lot.
i've heard that fetch 1st improves performance a lot, but i wonder why if it only affects the last stage of the select.
it will only improve the performance if i have a very simple ''where'', with no orderby, groupby and so, right?
thanks again.

[kolusu]

[jim haire]

Order by, group by, distinct and some other clauses cause sorts which take place before the fetch 1st would be performed. So if you were sorting a multi-million row table, the sort would be performed before any rows are fetched. Sorting often is the most time consuming part of a query.

The Fetch 1st could return a different set of rows every time if you don't sort.

[jctgf]

i did a test today.
i run a query that selected 42000 lines, with an orderby clause.
it took 5 seconds or so to run.
then i added a 'fetch 1st 1000 rows only" and to my surprise the time was about the same.
then a changed it to "100 rows only" and the time dropped to 2 secs.
no matter the value i used in the "fetch 1st", i could only see a significant diff is this value was very small.
i wonder why.
thanks.

[papadi]

Possibly because the higher volume causes actual i/o instead of being handled in-core.
_________________
All the best,

di

[jsharon1248]

Another possibility is that DB2 bypassed the sort and used an index to satisfy the ORDER BY requirements.

From the Implementing Indexes section in Chapter 4 of the Administration Guide

[dbzTHEdinosauer]

The OPTIMIZE FOR n ROWS clause could/should also be invesitigated.

when one is working with a LARGE table, one should design the indexes appropriately to prevent LARGE result sets that will not be used.
_________________
Dick Brenholtz
American living in Varel, Germany

[jctgf]

hi there,

i wonder if another possibility would be to use a freespace "big enough" when creating the table. it would reduce the chances of having a split of CI because of the inserts and, therefore, avoiding the table to become disorganized.

another thing, please: the primary key of this table is a sequenced number (i mean a number that is incremented everytime a new record is created). in this situation, new records would always be added to the end of the index/table and no split of CI would occur. consequently, the table would always remain physically sorted. is it correct?

thanks.

[papadi]

[IEFBR14]

"new records would always be added to the end of the index/table and no split of CI would occur."
As mentioned above why worry about CI splits? ...Now the Index tree may be an issue