DB2 index types

What types of indexes does DB2 database have? Next we will introduce you to the DB2 index type for classification. If you are new to the DB2 database, take a look. DB2 index type: Before introducing the index type, we will introduce the concept of density. density: when the data distribution is even, the density = possible number of data distributions/total number of data records. For example, in table 1, Index 1 is on column 1, where column 1 has 10 data distributions, ranging from 1 to 10. The data distribution is close to even distribution, and the total data volume is 1000, then the index's density is 100/1000 = 10%, and the highest density is 1. The smaller the density, the higher the index selectivity, the better the query performance. (1) Non-unique indexes can be said to be non-unique indexes of most indexes, which is related to the distribution of data. Generally, data is reproducible, so they cannot be defined as unique indexes. For non-unique indexes, run the create index <IDX_NAME> ON <TAB_NAME> (<COLNAME>) command. (2) The unique index is used to ensure data uniqueness. The performance of a unique index is generally higher than that of a non-unique index, which is related to the index density. The density of a unique index is always the reciprocal of the total number of data records. (3) The concept of pure index is relative to that of general indexes. In the following method, the table has two fields, where field 1 is the unique primary key and field 2 is the data. In actual queries, it is often the select * from table where col1 =? Such a query condition can use a pure INDEX to avoid table queries. The specific creation command is create unique index <IDX_NAME> ON <TAB_NAME> (COL1_NAME) INCLUDE (COL2_NAME ). The preceding statement creates a unique index on col1 and selects data containing col2. The additional data will be stored together with the key, but not as part of the index, so it is not sorted. Pure Index access is used to reduce access to data pages, because the required data is already displayed in the index. (4) The Cluster Index allows a more linear access mode to data pages, allowing more effective prefetch and avoiding sorting. A cluster index requires that you perform more operations when inserting data, and put the adjacent data entries on the same page to make the query faster, because every time you access an index page, you must access all the index entries before moving them to the next page. This ensures that only one index page exists at any time in the cache pool. Cluster Index features: Improves query speed, data pages are arranged in the order of keys; the entire table is scanned in the order of keys; more things need to be done for insertion and update, you are not advised to perform cluster indexes on tables that are inserted and updated frequently.