Apr 6 ’11
Technical Insights: Save MIPS With Effective DB2 for z/OS Indexes
The primary cause of inadequate DB2 application performance is inefficient SQL. Poorly performing SQL degrades response times and typically consumes unnecessary resources. One way to improve SQL performance is to add effective indexes.
A DB2 index is a list of the locations of rows sorted by the contents of one or more columns. Indexes improve query performance, but they come with a price. Indexes use disk space and increase the cost of insert, delete, and some update operations. When you design indexes, balance costs with performance. You need to maximize index matching while minimizing the number of indexes. You need to understand how queries are using indexes. Improving index usage could be the most significant performance enhancement you make.
You can create multiple indexes on a base table. However, the more indexes you have, the more DB2 must work to keep the indexes current. Creating multiple indexes for a table with heavy updates can slow processing and lead to longer outages during maintenance work-like reorganizations.
Let’s look at some types of indexes:
- It isn’t always important to control the physical sequence of the data in a table. But when you need to do so, use a clustering index.
- You can create an index that’s partitioned according to the partitioning scheme of the underlying data.
- Non-Partitioning Secondary Indexes (NPSIs) are indexes on partitioned tables. They’re different from the partitioning key, which is used to order and partition the data. One NPSI will reference the entire table space, spanning all partitions.
- Data Partitioned Secondary Indexes (DPSIs) provide more availability than NPSIs because they improve partition independence and reduce contention for concurrent utilities. Queries with predicates that reference columns in a single partition benefit from DPSIs.
- Index on expression allows you to use scalar expressions in place of columns in the definition of an index.
- DB2 10 allows you to add columns to an existing, unique index to allow for index-only access paths. This can eliminate the need for some indexes that are created for performance reasons when there aren’t enough columns for the existing, unique index.
When you have accurate catalog statistics and proper predicates, DB2 usually chooses the best index for each query. However, you may want to influence DB2 to use a particular index by:
- Coding an ORDER BY on index columns in index column sequence. If possible, DB2 will use an index to avoid a sort in support of an ORDER BY clause.
- Adding index columns to make the query index-only. If feasible, move all the columns from the table into the desired index.
- Increasing index match columns by promoting predicates, changing the order of columns in the index, adding or removing columns from the index, or adding predicate enablers.
- Disabling predicates on other indexable columns.
- Coding an OPTIMIZE FOR clause to tell DB2 how many rows you will use from a query.
In general, indexes improve query performance, but some indexes could actually degrade performance. How do you know which indexes you need and which indexes are causing performance problems and overhead?
A comprehensive index tool provides accurate, real-time performance information about DB2 indexes so you can evaluate the performance of your indexes and identify candidates for improvements. It collects and displays actual access counts for each unique SQL statement (table and index and predicate usage frequencies). It provides on-demand, dynamic data collection of index dependencies and catalog statistics.
An essential piece of an effective index tool is a “what if” index analysis that simulates the effects of adding, dropping, or updating statistics for an index with the use of cloned structures. Another valuable capability is the ability to analyze a DB2 workload and recommend additional indexes or indexes to be dropped based on actual execution.
By using indexes to improve SQL performance, balancing the cost of the index with the performance improvements, and looking for tools that help make the most of your DB2 indexes, you can improve DB2 application performance.