Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: Tips or keys to know that an index is ideal in db2 9.7

    Hello colleagues,
    Hello colleagues, someone can say to me tips or keys to know that an index is ideal in db2 9.7.
    Regards.
    DBA DB2 for LUW

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    To start with:
    Primary Key - always has a unique index
    Foreign Key's (even if RI is not implemented you should index logical RI)
    Add indexes for top queries
    For multi-column indexes I go with the columns that will be used by the most queries first. For example if you are going to index on first name, last name and some of the queries only use last name, put the last name first

    For BI systems there are additional rules depending if you are Star schema, snowflake etc.

    Try not to over index, nothing is more annoying then a 5,000 row table with 10 indexes.

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Hello,
    As soon as I believe(create) an index there is some way of knowing this index is ideal?
    There is some table that he(she) says to me if this index is the fact that I need for my search?

    Regards.
    DBA DB2 for LUW

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Look at the KPI's. How many rows read vs how many rows selected. What is the ratio between those figures? That is an indication for your index efficiency.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You may also look of LASTUSED column from SYSCAT.INDEXES
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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