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
How many alternate indexes impacts performance
Moderators: Kalicharan, Moderator Group
-
- Member
- Posts: 1
- Joined: Thu Feb 25, 2010 3:32 am
- Location: Columbus, GA
db2 index impact on performance
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.
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
Chennai
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page