DB2 index creation principles

DB2 index creation principle DB2 index implementation is a B + tree, through which you can quickly query, avoid full table scan to reduce IO operations. An index is an abstraction of table data. By extracting limited data and calculating the data distribution, you can quickly retrieve the data. Note: The index should be used to increase the query speed, but it will have a negative impact on the update and delete operations, because the index must be updated synchronously. Therefore, the index should be created to a table that is updated and deleted less than read. Indexes require independent space for storage and management. Indexes are stored in disk space. Therefore, avoid duplicate redundant indexes. As follows:

CREATE TABLE TEST_IDX (COL1 INT NOT NULL, COL2 INT NOT NULL, COL3 IN NOT NULL)。CREATE INDEX TEST_IDX_IDX1 ON TEST_IDX (COL1, COL2, COL3)

. There is already an INDEX TEST_IDX_IDX1 in three columns, and in the creation of create index TEST_IDX_IDX2 ON TEST_IDX (COL1, COL2), such an INDEX is generally useless. Indexes are used to avoid table scanning. A relatively small amount of ordered data structure is formed by extracting a limited part of a large amount of data through indexes. You can quickly search for the desired data by searching the ordered data structure. Therefore, the index is suitable for creating a table with a large amount of data, and the query on this table is often based on the condition to query part of the data. For example, some basic SYSTEM tables, such as SYSTEM tables, have a small amount of data and are often used to query all the data. Therefore, index creation on these tables does not have a great impact on performance and can be avoided completely, to avoid impact on management. Another purpose of creating an INDEX is to ensure data uniqueness. You can use "create unique index <INDEX_NAME> ON <TABLENAME> (<COLNAME>)" to CREATE an INDEX. The primary key creates an index implicitly, so do not create an index on the primary key to waste space. Minimize index creation. The DB2 path access optimizer estimates the cost of as many access paths as possible based on the indexes provided in the table. Creating too many indexes means that the DB2 optimizer generates more access paths and completes more estimation of the access plan cost, which increases the SQL statement Compilation Time. Creating a unique index can avoid sorting. Because the index is an ordered data structure, DB2 will output results in sequence by default during scanning, rather than insert sequence. By creating a unique index, you can avoid sorting and improve query performance. Do not create indexes for columns with large amounts of duplicate data. Creating an index on a large number of duplicate columns makes no sense. The following data structure: the Field col1 In the table contains a large number of duplicate data, where the data distribution is based on 90% Y, and 10% N. It makes no sense to create indexes for such columns. When the query condition is col1 = 'y', the index scan and table scan of the table are not significantly different. Based on practical experience, the data on the column should be evenly distributed and the extraction density cannot exceed 5 ‰. Note: You can specify the index tablespace when creating a table, for example:

create table tabname(...) in tabspace index in tabindexspace