optimize for n rows vs fetch first n rows.

[Omi]

Hi,

Is there any difference in performance if we use optimize for n rows vs fetch first n rows in DB2 on zOS? I found a link on DB2 but that applies to Db2 on windows: http://pic.dhe.ibm.com/infocenter/db2lu ... 55223.html.

[Anuj Dhawan]

The concept explained in the link is applicable to DB2 on zOS as well. Just to reiterate - Optimize for x rows tells DB2 that out of the entire result set, you want to get the first x rows as fast as possible! This tells DB2 sub-system to favor INDEXes that eliminate sorts, favor nested loops JOINs and siblings.

Fetch first x rows only, OTOH, tells DB2 to automatically close the cursor after x rows. So, fetch first x rows only automatically implies optimize for x rows.

Sometimes, you might want to use both, for example, if you need to fetch 1000 rows, but you need the first 10 very quickly to display on user interface, you can specify

Code: Select all

select
...
FETCH FIRST FIRST 1000 ROWS ONLY
OPTIMIZE FOR 10 ROWS

Hope this helps.

[Omi]

Anuj, thanks. I got to understand it better now.

[Anuj Dhawan]

You're welcome!