Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Question Unanswered: OPTIMIZE FOR N ROWS .... when to use

    When is a good time to use OPTIMIZE FOR N ROWS....i.e I have a lot of statements that I know are returning only 25 rows at a time....or statements that have RETURN FIRST ROW ONLY.

    In those cases is it a good time to add OPTIMIZE FOR on those statments?

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    My understanding (and I'm hoping if I'm wrong somebody will correct me!), is that optimising for 25 rows when your full result set will contain 25 rows would most likely make absolutely no difference to the access path.

    The idea of optimising for n rows is for when you are working with cursors. If you know that your cursor could potentially return 1000000 rows but you are only going to fetch the first n rows, optimising for n rows may produce a more efficient access path for your requirement.

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Damian Ibbotson
    My understanding (and I'm hoping if I'm wrong somebody will correct me!), is that optimising for 25 rows when your full result set will contain 25 rows would most likely make absolutely no difference to the access path.
    It could, it depends on how accurately DB2 can predict the cardinality of the query when it compiles the query... if it thinks there will be 10000 rows but in fact you tell it there will be 25, it could very well change the access path.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    It could also turn off sequential prefetch.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Quote Originally Posted by J Petruk
    It could, it depends on how accurately DB2 can predict the cardinality of the query when it compiles the query... if it thinks there will be 10000 rows but in fact you tell it there will be 25, it could very well change the access path.
    Now this is where my ignorance comes in... Surely if the stats are not up to date, there is no guarantee that telling it you know that the result set will return 25 rows would produce a more efficient access path, given that the 'new' path would still be based on inaccurate statistics?

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Damian Ibbotson
    Now this is where my ignorance comes in... Surely if the stats are not up to date, there is no guarantee that telling it you know that the result set will return 25 rows would produce a more efficient access path, given that the 'new' path would still be based on inaccurate statistics?
    I'm not suggesting the stats aren't up to date, but remember that the db2 optimizer does a lot of educated guess work to determine the best path.

    You have information it doesn't - ie. you may know there are very few residents on the continent of Antartica, where db2 assumes Antartica is a typical continent.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Sometimes OPTIMIZE FOR N ROWS is used to force an index scan .. Of course, there is no gurantee DB2 will, and also, there is no gurantee that an index scan may perform better.

    You have information it doesn't - ie. you may know there are very few residents on the continent of Antartica, where db2 assumes Antartica is a typical continent.
    And this is where your distribution statistics, numquantiles and numfreqvalues help

    Cheers
    Sathyaram
    Last edited by sathyaram_s; 08-13-04 at 10:01.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by sathyaram_s
    And this is where your distribution statistics, numquantiles and numfreqvalues help
    Sure Just an example, point being that humans sometimes know more about a query result than the database can, short of executing it.

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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