Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

optimize for 1 row meant for?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
lalitha.rayala5

New User


Joined: 25 Aug 2010
Posts: 10
Location: Bangalore, India

PostPosted: Thu Sep 02, 2010 4:17 pm    Post subject: optimize for 1 row meant for?
Reply with quote

Hi,

I have a query as below

declare csr1 cursor for
select ibusrea , ipcod from hbw.berjkv
where ctabbw = 'abcd'
order by ibusrea desc , ipcod desc optimize for 1 row.

how does "optimize for 1 row" work here? As per my code after fetch only first 2 rows are being used.
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Sep 02, 2010 7:36 pm    Post subject:
Reply with quote

OPTIMIZE FOR 1 ROW tells DB2 to select an access path that returns the first qualifying row quickly. This means that whenever possible, DB2 avoids any access path that involves a sort.

In "OPTIMIZE FOR n ROWS", if you specify a value for n that is anything but 1, DB2 chooses an access path based on cost, and you won't necessarily avoid sorts.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Sep 02, 2010 7:37 pm    Post subject:
Reply with quote

Okay, i hadn't had this handy, please look here: http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_optimizefornrows.htm
Back to top
View user's profile Send private message
lalitha.rayala5

New User


Joined: 25 Aug 2010
Posts: 10
Location: Bangalore, India

PostPosted: Fri Sep 03, 2010 11:07 am    Post subject: Reply to: optimize for 1 row meant for?
Reply with quote

"OPTIMIZE FOR 1 ROW tells DB2 to select an access path that returns the first qualifying row quickly".

In the above statement which will be the first qualifying row? In the query above, generally DB2 will get all the qualifying rows first and then it will do the sort for ibusrea and ipcod. After sorting i will display all the sorted qualifying rows list. Is it the first row from this list? or the first row from the qualifying rows before sort?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Sep 03, 2010 12:43 pm    Post subject:
Reply with quote

if possible db2 will avoid the sort :
if an index exists on (ibusrea desc , ipcod desc,... ) or on (ctabbw, ibusrea desc , ipcod desc,... ) , it will get the rows via that index , eliminating the need for sort.
If no suitable index is found, OF1R does not have any effect.
Back to top
View user's profile Send private message
lalitha.rayala5

New User


Joined: 25 Aug 2010
Posts: 10
Location: Bangalore, India

PostPosted: Wed Sep 08, 2010 5:02 pm    Post subject: Reply to: optimize for 1 row meant for?
Reply with quote

ok thanx..
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Optimize sort inrec parse vishwakotin DFSORT/ICETOOL 6 Mon Jun 26, 2017 11:15 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am
No new posts DB2 history to optimize SQL path? John Poulakos DB2 2 Tue Jun 25, 2013 7:56 pm
No new posts How to use OPTIMIZE options in PL/I f... CyberKing PL/I & Assembler 3 Thu Jan 31, 2013 9:56 pm
No new posts How to optimize DB2 SELECT query to r... sandip_mainframe DB2 3 Tue Jan 08, 2013 3:45 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us