Results 1 to 6 of 6

Thread: making index

  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: making index

    how can we choose that on which column is to make index to make search more efficient

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You look at the WHERE clause of you queries. Columns mentioned there are good candidates for indexes.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Are you interested in the CREATE INDEX statement?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ankur02018
    how can we choose that on which column is to make index to make search more efficient
    DB2 (unlike some other databases) has an excellent optimizer, and should chose the correct index so long as it has accurate statistics (from a runstats command). If you don't think that DB2 is chosing the correct index, then open a problem with IBM support.

    If DB2 choses the wrong access path because the number of rows in the table varies drastically within a short timespan, alter the table and set it to volatile.
    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 2007
    Posts
    12
    hi ankur,

    the Db2 design advisor can suggest you on on which indexes u can create for better performance.

    db2advis -d database -s "sql statment"

    u can get more about design advisor by refering this

    http://www.ibm.com/developerworks/db...arthur/#design

  6. #6
    Join Date
    Dec 2006
    Posts
    6
    Hi,

    You can actually see the columns on which the maximum number of hits are being done and decide the columns that require an index.

Posting Permissions

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