Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013
    Posts
    12

    Unanswered: what is the use of creating multiple indices on the same table?

    we have a table, which consists of multiple indexes with different combinations on the columns. what is the use of them in performance prospective. whether they will increase or decrease the performance?
    we have indexes like this
    INDEX_NAME COLUMN(S)
    ------------ ------------
    IDX_Quote Quote_Number
    IDX_Quote1 Assigned_Agent+BOUND_IND+AGENT_DEL
    IDX_QUote2 LAST_NAME+DOB+Quote_Number+ZIp_COde
    IDX_Quote3 LAST_NAME+DOB+EMAIL+ZIP_COde
    IDX_Quote4 DOB+LAST_NAME_ZIP_COde+Quote_Eff_DT

    What is the use of these many Indexes??
    Thanks in advance....

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Indexes are a trade-off of storage and performance to maintain the index versus the convenience of accessing the data using the index. While there is a physical limit on how many indexes can be used on a table or in a database, there are also practical limits that will constrain the indexes long before you reach the limit that DB2 will support!

    Each index orders the rows in the table according to the columns specified. When queries need to access the data, they can often use the index to limit the number of rows that need to be considered. So:
    Code:
    SELECT *
       FROM Quote
       WHERE FIRST_NAME = 'Pat'  --  Table scan, slow
    
    SELECT *
       FROM Quote
       WHERE FIRST_NAME = 'Pat'
          AND LAST_NAME = 'Phelan'  --  Can use IDX_QUote2 or IDX_Quote3, so fast
    As long as your indexes support your queries and their benefit outweighs their cost, I'm in favor of keeping them!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by vasu_479 View Post
    we have a table, which consists of multiple indexes with different combinations on the columns. what is the use of them in performance prospective. whether they will increase or decrease the performance?
    we have indexes like this
    INDEX_NAME COLUMN(S)
    ------------ ------------
    IDX_Quote Quote_Number
    IDX_Quote1 Assigned_Agent+BOUND_IND+AGENT_DEL
    IDX_QUote2 LAST_NAME+DOB+Quote_Number+ZIp_COde
    IDX_Quote3 LAST_NAME+DOB+EMAIL+ZIP_COde
    IDX_Quote4 DOB+LAST_NAME_ZIP_COde+Quote_Eff_DT

    What is the use of these many Indexes??
    Thanks in advance....
    Indexes 2 & 3 are probably not both needed. However, if you don't care about index overhead (cost of creating the index when rows are inserted into the table and the cost of storage) then having both might yield slightly better performance.
    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
  •