Improved New Features of DB2 V9.7 partitioned indexes for large databases

This article mainly describes the new features of DB2 V9.7 partitioned index to improve the actual operational performance of large databases, including how to correctly use and manage partition indexes, there are also the actual steps to improve the performance of large databases by using partition indexes.

Introduction

Partition index partitioned index) is a new feature in DB2 V9.7. This article describes what is partition index, how to create and manage DB2 V9.7 partition index, and how to improve the performance of large databases, readers will get a first-hand experience on partition indexes. Each DB2 V9.7 partition index consists of multiple index partitions (index partition). Each index partition only indexes the data in the corresponding data partition.

Before getting started

Before discussing partition indexes, we need to review the table partition features of DB2. This feature was introduced in DB2 V9, this article on developerworks, "DB2 9 Table Partitioning-improving the management of large databases" is a good reference.

Table Partitioning is a data organization mode in which data is divided into multiple partitions or ranges based on the values of one or more columns). Each Data Partition is stored separately. These storage objects can be located in different tablespaces, in the same tablespace, or in a combination of these two situations.

Table Partitioning improves the management of large databases, allowing you to flexibly place indexes. In Figure 1, two indexes are created on the partition table, and each index is placed in one tablespace. However, we can also see that the key value in each index points to the database of all data partitions, and the index will become large when the table data volume is large.

Figure 1. Table partition features and indexes in DB2 v9

In addition, the TABLE partition feature allows you to use alter table... The attach partition command and the detach partition command easily implement roll-in and roll-out of table data. Neither of these operations requires any data movement, this greatly improves the performance. At the same time, we can see that the indexes need to be maintained after these two operations. For example, after ATTACH a new partition, We need to index the new data of the partition, after DETACH a partition, you need to clear the corresponding key values in the index.

Partition index Overview

Before DB2 V9.7, indexes in partitioned tables cannot be partitioned. In many cases, partition tables are used in the data warehouse environment. When the data volume is large, the index also increases, leading to performance reduction.

In DB2 V9.7, the index can also be partitioned, which is called the partition index partitioned index ). DB2 V9.7 partitioned index consists of multiple index partitions index partition). The key value in each index partition points to the data partition of the corresponding unique data partition, the index created by the system or the index created by the user can be a partition index.

In Figure 2, three indexes are created on a partition table with four data partitions. index1 and index2 are partition indexes, which are composed of four index partitions, index3 is a non-partition index nonpartitioned index, or global index). Correspondingly, we can call a partition index a local index ).

Figure 2. Partition index in DB2 V9.7

A significant advantage of Partitioned indexes is that when you use the alter table attach partition and detaich partition commands to transfer data to roll-in) and roll-out, using partition indexes can greatly improve performance.

In DB2 V9.7, the following types of indexes cannot be partition indexes, but can only be non-DB2 V9.7 partition indexes.

XML Index

Spatial data) Index

MDC block index, which is generated by the system)

Index generated by the XML path index System)

Preparations

Before starting, create a new database named MYDB, as shown in Listing 1. Of course, you can use an existing database. However, to simplify the environment, we recommend that you use a new database.

All operations in this article are performed in DB2 V97 on the LinuxAMD64 platform, and versions earlier than V97 do not have the partition index feature of DB2 V9.7.

List 1. Create a database

Db2 create db mydb

After the database is created, several tablespaces are created, as shown in Listing 2.

List 2. Create a tablespace

 
 

  
  
CREATE TABLESPACE TbspT MANAGED BY DATABASE using (FILE 'tspT' 4 M) AUTORESIZE YES;   
  
  
CREATE TABLESPACE TbspX MANAGED BY DATABASE using (FILE 'tspX' 4 M) AUTORESIZE YES;   
  
  
CREATE TABLESPACE TbspD MANAGED BY DATABASE using (FILE 'tspD' 4 M) AUTORESIZE YES;   
  
  
CREATE TABLESPACE TbspY MANAGED BY DATABASE using (FILE 'tspY' 4 M) AUTORESIZE YES;   
  
  
CREATE TABLESPACE TbspW MANAGED BY DATABASE using (FILE 'tspW' 4 M) AUTORESIZE YES;   
 
 

For details, visit:

Http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0907haoqy/