Results 1 to 2 of 2
  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 alot 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
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ApoPen
    When is a good time to use OPTIMIZE FOR N ROWS....i.e I have alot 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?
    OPTIMIZE FOR N ROWS gives the optimizer a hint as to the cardinality of the result, as well as determining the block size for communication between client and the db2 server.

    Use it whenever you know how many rows will be returned, as long as you don't mind going through and updating it when things changed.

    Don't get it wrong, though, since if more rows are returned there could be a negative impact on communications (more back-and-forth to retreive the result set) and on query optimization.
    --
    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
  •