DB2 index design guidelines

1. If a table has a large number of indexes, the performance of insert, update, and delete statements will be affected, because all indexes must be adjusted properly when the data in the table is changed. On the other hand, for queries that do not need to modify data (select Statement), a large number of indexes will help improve performance, because the database has more indexes to choose from, in order to determine the best way to access data as quickly as possible.

2. Composite Index: A composite index contains multiple columns. A composite index is equivalent to multiple single-column indexes. For example, indexes (cola, COLB, colc) are at least equivalent to (COLA), (cola, COLB, colc) three indexes.

2. Overwrite queries can improve performance. Overwrite query means that all specified columns in the query are included in the same index (composite index. For example, if a composite index is created on columns A, B, and C of a table, the queries for columns A and B in the table are considered as overwrite queries. Creating an index that overwrites a query can improve the performance, because all the data in the query is included in the index itself. When retrieving data, you only need to reference the index page of the table and do not need to reference the data page, therefore, the total I/O volume is reduced. Although adding columns to the index to overwrite the query can improve the performance, maintaining more columns in the index will incur the update and storage costs.

3. indexing a small table may not produce any optimized results, because it may take longer time for the database to traverse the index to search for data than to scan a simple table.

4. Use the SQL event probe and index Optimization Wizard to help analyze and query and determine the index to be created. It is very complicated to select the correct index for the database and its workload. A balance must be achieved between the query speed and the update cost. Narrow indexes (the index of only a few columns in the search keyword) require less disk space and maintenance costs. On the other hand, wide indexes can cover more queries. There are no simple rules for determining the correct index set. Experienced database administrators are often able to design good index sets. However, this task is complex, time-consuming, and error-prone even for less complex databases and workloads. You can use the index optimization Wizard to automate this task. For more information, see index Optimization Wizard.

5. You can specify indexes on The View.

6. You can specify an index on the calculation column.

7. Avoid using is null and is not null in the index column. To avoid using any columns that can be empty in the index, the database will not be able to use the index. This record does not exist in the index if the column contains a null value. For composite indexes, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index.

8. If you frequently search for less than 15% rows in a table that contains a large amount of data, you need to create an index.

9. 95/5 rules for measuring index efficiency: if the number of rows returned by the query result is less than 5% of all rows in the table, the index is the fastest way to retrieve data. If the query result exceeds 5%, the index is usually not the fastest way.

10. The column where the primary and unique keywords are located automatically has an index, but the external keywords are not automatically indexed.

2. After determining that an index is suitable for a specific query, you can customize the index type that best suits the specific situation. Index features include:

● Clustering or non-clustering ● unique or not unique ● single or multiple columns ● the column order in the index is ascending or descending (the index is ascending by default, however, most large databases currently support reverse indexing. ● overwrite or not overwrite ● you can also customize the initial storage features of indexes and optimize maintenance by setting the fill factor, and use files and file groups to customize their locations to optimize performance. ● Bitmap Index)