Results 1 to 4 of 4
  1. #1
    Join Date
    May 2010
    Posts
    3

    Unanswered: OPTIMIZE for N rows

    What are the disadvantages of Clause "OPTIMIZE FOR N ROWS" in SQL query?

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The main benefit is when you open a cursor and only fetch some of the rows (before getting to end of cursor with SQLCODE = 100). Using OPTIMIZE FOR 1 ROWS will turn off sequential prefetch (where DB2 retrieves rows from the tablespace and puts them in the bufferpool even before they are requested, in anticipation that they will be needed soon). But if all the rows in the cursor are not fetched, then it would be a big waste to invoke sequential prefetch. Sequential prefetch usually only occurs when DB2 determines that a large number of rows will be included in the answer set (such as with with a table scan).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2010
    Posts
    3
    Thanks Marcus.

    How far can we believe on EXPLAIN tool for Db2 sql queries?
    Last edited by prakash5686; 06-03-10 at 02:04.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by prakash5686 View Post
    Thanks Marcus.

    How far can we believe on EXPLAIN tool for Db2 sql queries?
    I don't know what you are asking. Generally the explain is accurate if done on the same system as the query is run. But you could get different results if your literals are different in the query than in the explain, or if parameter markers are used in the explain and literals are used in the query.

    Also, the DB2 optimizer only estimates how many rows will be returned, and could be wrong based on the quality of statistics captured with runstats. But the access path is likely to be the same (index, table scan, etc).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •