Correct Process Description for creating local partition indexes in DB2 V9.7

The following article describes how to create a local partition index in DB2 V9.7. We all know that a partition table can have both a partition index and a non-partition index. If you want to 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 TBSPACEIDT1 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, does not indicate that the tablespace corresponding to this ID exists.

Listing 5. Index partition information

 
 

  
  
db2 "select substr(INDNAME,1,18) idx_name,DATAPARTITIONID from syscat.indexpartitions"   
  
  
IDX_NAME DATAPARTITIONID   
  
  
IDX_T1_L_PARTKEY 0   
  
  
IDX_T1_L_PARTKEY 1   
  
  
IDX_T1_L_PARTKEY 2   
  
  
IDX_T1_L_PARTKEY 3   
 
 

We can see that the index IDX_T1_L_PARTKEY is divided into four partitions. The tablespace that stores the index is the same as the tablespace in the Data Partition. For example, the index IDX_T1_L_PARTKEY is the first partition that only references the 0QUARTER01 partition, data is stored in DMS_D1. Note that when creating a partition index, you cannot directly specify the tablespace used to store the index partition. The tablespace is determined by the tablespace specified for the Data Partition when creating the table.

By default, the default storage location of a partition is the same as that of the Data Partition it references.

Listing 6. specifying a tablespace for the index

 
 

  
  
CREATE TABLE t1 (columns) in ts1 INDEX IN ts2 1   
  
  
PARTITION BY RANGE (column expression) (PARTITION PART0 STARTING   
  
  
FROM constant ENDING constant IN ts3,   
  
  
PARTITION PART1 STARTING FROM constant ENDING constant INDEX IN ts5,   
  
  
PARTITION PART2 STARTING FROM constant ENDING constant INDEX IN ts4,   
  
  
PARTITION PART3 STARTING FROM constant ENDING constant INDEX IN ts4,   
  
  
PARTITION PART4 STARTING FROM constant ENDING constant)   
  
  
CREATE INDEX x1 ON t1 (...) NOT PARTITIONED;   
  
  
CREATE INDEX x2 ON t1 (...) PARTITIONED;   
  
  
CREATE INDEX x3 ON t1 (...) PARTITIONED;  
 
 

In the preceding example, the non-partition index X1 is stored in table space TS2, because the default table space TS2 is specified for all non-partition indexes in Table T1.

The index partition corresponding to partition 0 of partition index X2 and X3 is stored in table space ts3. this is because the default position of the index partition is the same as that of the referenced data partition. The index partitions corresponding to data partitions 1 and 2 are stored in table space ts4. this is because these two data partitions clearly indicate the tablespace that stores the index. The index partition corresponding to partition 4 is stored on ts1. this is because we didn't specify a storage tablespace for partition 4. The default storage tablespace is ts1.

After DB2 V9.7, all indexes created by the user are partition indexes by default. When creating a non-unique partition index, DB2 will automatically use the partition key for partitioning. When creating a unique partition index, the index column must contain all the columns used for the partition. Otherwise, DB2 returns the SQL20303N error, indicating that the index creation failed.