Results 1 to 6 of 6

Thread: DB2 Optimizer

  1. #1
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Unanswered: DB2 Optimizer

    Why does DB2 optimizer selects Table scan though there are indexes.
    Does any one have any clues when will the DB2 optimizer uses indexes.
    If i know using indexes will increase the performance of Query then how can i make optimizer use indexes. Is there any cheat sheet

  2. #2
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: DB2 Optimizer

    The database maintains statistics on your tables that are used by the optimizer on the selection of the best execution plan. Perhaps the statistics of your table are too old, which causes the DB2 to understand they´re outdated, and chooses performing a table scan other than using the index.

    Have you tried updating the statistics of your tables?
    It´s also a good idea to reorganize your table.

    What version of DB2 are you running?

    In DB2 V7 you can only reorganize your table using one index. But I think in V8 you can choose more than one, not really sure.

    If this is really the cause of the table scan being chosen instead of the index use, try first reorganizing the table using the command 'REORG TABLE' and then updating the statistics of your tables, issuing the command 'RUNSTATS ON TABLE'.

    For more information on these commands, refer to Command Reference or Administration Guide.

    HTH,
    Fernando
    Originally posted by koganti
    Why does DB2 optimizer selects Table scan though there are indexes.
    Does any one have any clues when will the DB2 optimizer uses indexes.
    If i know using indexes will increase the performance of Query then how can i make optimizer use indexes. Is there any cheat sheet
    Last edited by F.OHANA; 02-24-03 at 13:00.

  3. #3
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    Iam using V7.2

    We update statistics every night and also did the REORG.
    Still iam seeing the table scan.
    How to make it Use indexes like we do in ORACLE (using hints).

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78
    AFAIK there is no explicit command to force DB2 to make use of the indexes. What I often do is to organize the 'select list' and the 'where clause' to make the columns appear in the same order as they´re in the index.

    For example, I have table T1 with columns C1, C2, C3. And an index on C3,C2.

    When Writing the query, try putting the columns in the index, on the same order as they are in the index, this should work as a hint to DB2 to use the index.

    select C3, C2, C1 From...

    That works for me, hope it helps.

    Fernando

    Originally posted by koganti
    Iam using V7.2

    We update statistics every night and also did the REORG.
    Still iam seeing the table scan.
    How to make it Use indexes like we do in ORACLE (using hints).

  5. #5
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170
    I heard you can cheat the optimizer by updating statistics tables. If this is right what would be the right values to go with

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    On Widnows you can try using the "Index Wizard". On Control Center click to the pluses to get the Index folder. Then Create | Index using Wizard.

    The Index Wizard suggest creation of indexes that most satisfy query (or queries) performance. Try this I was impresed.

    You can also use the Visual Explain to see if your query is using indexes.
    The "Visual Explain Tutorial" is at the IBM support site http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main
    This tutorial is for DB2 v. 8, but I read it and the suggestions works for DB2 v. 7 too.

    Hope this helps,
    Grofaty

Posting Permissions

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