DB2 index design guidelines

This article mainly describes the DB2 index design principles and the descriptions of Related Matters worth attention in actual operations. The following is a detailed analysis of the main content of this article, we hope you will have a better understanding of the DB2 index design principles.

On the other hand, for queries that do not need to modify data in the SELECT statement), a large number of indexes can 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.

1. 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 index search keywords only have a small number of column indexes) requires 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.

Index features

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

Clustering or non-Clustering

Unique or not

Single or multiple columns

The column order in the index is ascending or descending (the index is ascending by default, but currently most large databases can support reverse indexing)

Overwrite or Not Overwrite

You can also customize the initial storage features of the index, optimize maintenance by setting the fill factor, and customize the location of the index using files and file groups to optimize performance.

Bitmap Index)

The above content is an introduction to the DB2 index design guidelines. I hope you will get something better.

The above content is an introduction to the DB2 index design guidelines. I hope you will get something better.