Page 1 of 1
optimize for n rows vs fetch first n rows.
Posted: Mon Sep 29, 2014 6:38 pm
by 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.
Re: optimize for n rows vs fetch first n rows.
Posted: Tue Sep 30, 2014 1:54 pm
by 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.
Re: optimize for n rows vs fetch first n rows.
Posted: Wed Oct 01, 2014 2:15 pm
by Omi
Anuj, thanks. I got to understand it better now.
Re: optimize for n rows vs fetch first n rows.
Posted: Wed Oct 01, 2014 4:55 pm
by Anuj Dhawan
You're welcome!
