Detailed description of the local partition index of DB2 V9.7

This article focuses on the introduction of local partition indexes in DB2 V9.7. the DB2 V9.7 database introduces the concept of partition indexes in a range partition table, this mainly adds a new feature selection for the customer based on the original global index.

The partition Index uses the B tree structure, which breaks down a global tree into a nokan tree. The hierarchy of the tree will be significantly reduced, this improves the performance of data insertion, update, deletion, and scanning. The partition index does not need to be re-built when the Roll in/Roll out of the partition table, accelerating data rollback.

Introduction

Before DB2 V9.7, a Range Partition Table only supports global indexes or non-partition indexes. This way, even if the data in the Partition Table is distributed across multiple tablespaces, all indexes of a partitioned table can only be stored in the same tablespace. This feature limits that index scanning can only read the index pages in one tablespace. As a result, too many pages are read and the scanning concurrency is limited.

DB2 V9.7 uses the partitioned index organization scheme, that is, the index data is distributed to multiple index partitions according to the table Partition Scheme. Each index partition only references the table rows in the corresponding data partition. From DB2 V9.7, a partition index is created by default when an index is created, unless the following conditions occur:

UNIQUE is specified for the create index statement, and the INDEX key does not include all table partition key columns.

Create an index based on spatial data.

Create an index based on XML data

Create a partition Index

A partitioned table can have both partition indexes and non-partition indexes. If you create a partition index, each index partition will contain the index entries of a single data partition, and the RID in the index leaf node will only point to a single data partition.

When we use the Attach Partition clause of the Alter table statement to Roll in or Out a Partition table, the Partition index is particularly useful. If a non-partition index is used, you must first issue the Set Integrity statement to enable the data in the newly added partition to go online. This process may consume a lot of time and log space. When partitioned indexes are used, these overhead will be eliminated.

Each partition of the partition index is stored in the B-tree structure. Because a large B-tree after the partition is divided into several small B-trees, the number of layers of the tree will decrease, this improves data insertion, update, deletion, and scanning performance. At the same time, when we execute the query, DB2 will use the partition elimination Optimization Method to Improve scanning performance and parallelism. Partition elimination technology helps the optimizer filter out unwanted index partitions and scan the corresponding partitions to complete the query. This is more efficient than Scanning Non-partitioned indexes.

List 1. Create an example Partition Table and Index

 
 

  
  
CREATE TABLE t1 ( l_orderkey INTEGER, l_partkey INTEGER, l_suppkey INTEGER,  
  
  
l_shipdate date, padding1 char(30) )   
  
  
PARTITION BY RANGE(l_shipdate)   
  
  
(   
  
  
partition quarter01 STARTING '2008-01-01' ENDING '2008-03-31'in DMS_D1,   
  
  
partition quarter02 STARTING '2008-04-01' ENDING '2008-06-30'in DMS_D2,   
  
  
partition quarter03 STARTING '2008-07-01' ENDING '2008-09-30' in DMS_D3,   
  
  
partition quarter04 STARTING '2008-10-01' ENDING '2008-12-31' in DMS_D4   
  
  
);   
  
  
Create index idx_t1_l_orderkey on t1(l_orderkey) NOT partitioned ;   
  
  
Create index idx_t1_l_partkey on t1(l_partkey) partitioned;   
 
 

In the code above, we created a range partition table with four partitions and two indexes at the same time. Index idx_t1_l_orderkey is a non-partitioned index, and idx_t1_l_partkey is a partitioned index.

Next, let's take a look at the description of table T1 and Its indexes in the data dictionary after the code is executed. The System View syscat. datapartitions contains the partition information and related statistics of the partition table. The System View syscat. indexpartitions contains the partition information and related statistics of the partition index.

List 2. Partition Table Data Partition Information

 
 

  
  
db2 "select substr(TABNAME ,1,10) t_name,substr(DATAPARTITIONNAME ,1,10) part_name,   
  
  
DATAPARTITIONID ,substr(LOWVALUE,1,12) low_value,substr(HIGHVALUE,1,12) high_value   
  
  
from syscat.datapartitions where tabname='T1'"   
  
  
T_NAME PART_NAME DATAPARTITIONID TBSPACEID LOW_VALUE HIGH_VALUE ----------   
  
  
T1 QUARTER01 0 8 '2008-01-01' '2008-03-31'   
  
  
T1 QUARTER02 1 9 '2008-04-01' '2008-06-30'   
  
  
T1 QUARTER03 2 10 '2008-07-01' '2008-09-30'   
  
  
T1 QUARTER04 3 11 '2008-10-01' '2008-12-31'   
 
 

The code in Listing 2 executes the query and obtains information about data partitions from the System View syscat. datapartitions. The query results show that table T1 has four partitions with the same names as when we created the table. The IDS are 0, 1, 2, and 3 in sequence, and the data in the tablespace DMS_D1ID is 8) DMS_D2ID is 9), DMS_D2ID is 10), and DMS_D3ID is 11. In this example, the tablespace information is as follows:

Listing 3. tablespace Information

 
 

  
  
db2 "select substr(TBSPACE,1,18) tablespace_name,TBSPACEID ,TBSPACETYPE   
  
  
from syscat.tablespaces"   
  
  
TABLESPACE_NAME TBSPACEID TBSPACETYPE   
  
  
SYSCATSPACE 0 D TEMPSPACE1 1 S USERSPACE1 2 D   
  
  
IBMDB2SAMPLEREL 3 D IBMDB2SAMPLEXML 4 D SYSTOOLSPACE 5 D   
  
  
SYSTOOLSTMPSPACE 6 S TBS_TEST 7 D   
  
  
DMS_D1 8 D DMS_D2 9 D DMS_D3 10 D DMS_D4 11 D DMS_I1 12 D   
 
 

Listing 4. Basic index information

 
 

  
  
db2 "select substr(tabname,1,10) tab_name, substr(INDNAME,1,18) inx_name,TBSPACEID   
  
  
from syscat.indexes where tabname='T1'"   
  
  
TAB_NAME INX_NAME TBSPACEID ---------- ------------------ -----------   
  
  
T1 IDX_T1_L_ORDERKEY   
  
  
8   
  
  
T1 IDX_T1_L_PARTKEY 65530   
 
 

We can see that the non-partition index IDX_T1_L_ORDERKEY data is stored in the tablespace DMS_D1 and the ID is 8), which means that when the index is not explicitly specified, DB2 uses the tablespace where the first data partition is located to store non-partition indexes. The ID of the tablespace where the index IDX_T1_L_PARTKEY is located is 65530. From the tablespace Information Section, we can see that there is no ID 65530 tablespace in our database.

Because the Storage Feature of the partition index is associated with the Data Partition, one index will be distributed in multiple tablespaces, so DB2 uses 65530 special values to represent the index tablespace, it does not mean that the tablespace corresponding to this ID exists. The above content is an introduction to the local partition index of DB2 V9.7. I hope you will get something.

For details, visit:

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