How many alternate indexes impacts performance

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
sleatherwood
Member
Posts: 1
Joined: Thu Feb 25, 2010 3:32 am
Location: Columbus, GA

How many alternate indexes impacts performance

Post by sleatherwood » Thu Feb 25, 2010 3:36 am

I have a table that has approximately 9 million rows and 9 alternate indexes. Rows are 177 characters. My DBA says I cannot add 2 more alternate indexes to this table without significantly impacting performance but he cannot say WHY. While I realize it will have some impact, especially if I am doing inserts but 90% of transactions will be updates. Can you provide information on resources that would help me understand the true impacts? I just want to make an informed decision.

Thank you

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

db2 index impact on performance

Post by Natarajan » Thu Feb 25, 2010 12:38 pm

Here are some points , which may be helpful to you.

Impact of indexes on performance and disk usage....

1) Each index requires storage. The exact amount depends on the size of the table and the size and number of columns in the index.

2) Each INSERT or DELETE operation performed on a table requires additional updating of each index on that table. This is also true for each UPDATE operation that changes the value of an index key.

3) The LOAD utility rebuilds or appends to any existing indexes.

4) Each index potentially adds an alternative access path for a query for the optimizer to consider, which increases the compilation time.
Natarajan
Chennai

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others