Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    96

    Unanswered: with or without Optimize for clause

    Hi All,

    ENV: Db2 9.5 FP5, RedHat Linux 5.3

    I have a statment that sometimes takes too long to or not complete at all:

    "Select auth_id from db2inst1.datamodel order by auth_id desc fetch first 1 rows only optimize for 1 rows"

    There is an index that exists for auth_id and the explain plan reveals that the index is being used. The table datamodel isn't very big and has only 500 some rows in it.

    Would it be better for performace to NOT use the optimize for clause in this situation?

    Any other pointers.

    Thanks

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    rangupt, why don't you just use:
    Code:
    SELECT MAX(AUTH_ID)
    FROM db2inst1.datamode
    If the Index allows a reverse scan (and AUTH_ID is the first or only key), this should be a 1 fetch index access (about the best you can get)

  3. #3
    Join Date
    Jan 2008
    Posts
    96
    Thanks Stealth_DBA

    The access plan for all the 3 statements (MAX(auth_id), without Optimize clause and with optimize clause) reveals the exact same estimated cost.

    I ran application snapshot and db2pd to collect some information and found out that this particular statement (the originial one with 'Optimize for' clause) has way over 300,000 NumRef and NumExec.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What I'm wondering is why DB2 chooses an index access for a table with just 500 rows. If the table doesn't have many columns (which I don't know), a table scan may be an option as well. Do you have current statistics?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2008
    Posts
    96
    Quote Originally Posted by stolze View Post
    Do you have current statistics?
    Thats another thing I don't understand. The table as i said is quite small less than 500 rows. Yes the statistics were updated over the weekend.

  6. #6
    Join Date
    Jan 2008
    Posts
    96
    Quote Originally Posted by stolze View Post
    If the table doesn't have many columns (which I don't know)
    The table has 8 columns auth_id being the primary key.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The only column being selected is auth_id, which is in the index for the PK and there is no WHERE clause. So DB2 can satisfy the query with "index only" access, although it will scan all the leaf pages of the index instead of using the b-tree.
    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
  •