DB2 V9.7 partition index space usage and scan Performance description

We are going to discuss with you today the usage and scanning performance of DB2 V9.7 partition indexes. We all know that partition tables are mainly used in the context of large tables, therefore, we can use a large table to test the performance. Create a test table.

Listing 7. Creating a test table

 
 

  
  
drop table t1;   
  
  
CREATE TABLE t1   
  
  
( l_orderkey INTEGER NOT NULL, l_partkey   
  
  
INTEGER, l_suppkey INTEGER, l_shipdate date, padding1 char(30)   
  
  
)   
  
  
PARTITION BY RANGE(l_shipdate)   
  
  
(   
  
  
STARTING '2008-01-01' ENDING '2008-12-31' EVERY 1 MONTH   
  
  
)   
  
  
;   
  
  
INSERT INTO t1 (l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1)   
  
  
WITH TEMP (COUNTER, l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1) AS   
  
  
( VALUES (0, MOD(INT(RAND() * 12000000), 25), MOD(INT(RAND() * 12000000), 30),   
  
  
MOD(INT(RAND() * 12000000), 30), DATE(MOD(INT(RAND() * 12000000), 366)+733042), 'A')   
  
  
UNION ALL SELECT (COUNTER + 1), MOD(INT(RAND() * 12000000), 25),   
  
  
MOD(INT(RAND() * 12000000), 30), MOD(INT(RAND() * 12000000), 30),   
  
  
DATE(MOD(INT(RAND() * 12000000), 366)+733042), 'A' FROM TEMP   
  
  
WHERE (COUNTER + 1) < 12000000   
  
  
)   
  
  
SELECT l_orderkey, l_partkey, l_suppkey,l_shipdate,padding1   
  
  
FROM TEMP   
  
  
;   
 
 

The table we created contains 12 million rows of data, one partition per month of the month. The data in the partition column l_shipdate is evenly distributed between '2008-01-01 'and '201712-31. Note that 733042 is the expression of the number of days in DB2 on February 1, obtained through the days () function.

We first create a non-DB2 V9.7 partition index on the column l_orderkey.

Listing 8. Creating a non-partition Index

 
 

  
  
db2 "Create index idx_nopart_l_orderkey on t1(l_orderkey) not partitioned"   
  
  
db2 "runstats on table db2inst1.t1 and indexes all"   
  
  
db2 "select substr(INDNAME,1,25) idx_name,NLEVELS,NLEAF,INDCARD   
  
  
from syscat.indexes where tabname='T1'"   
  
  
DX_NAME NLEVELS NLEAF INDCARD   
  
  
IDX_NOPART_L_ORDERKEY 3 16831 12000000   
 
 

Listing 8 shows that the height of the non-partitioned index B tree is three layers, with 16831 leaf pages.

Listing 9. Test non-DB2 V9.7 partition index performance

 
 

  
  
db2 set current explain mode yes   
  
  
db2 values current timestamp   
  
  
1   
  
  
2009-07-07-15.46.24.863000   
  
  
db2 "select count(*) from t1 "   
  
  
1   
  
  
----------- 12000000   
  
  
db2 values current timestamp   
  
  
1   
  
  
2009-07-07-15.46.27.394000   
  
  
db2exfmt -d sample -w -1 -n % -s % -# 0 -t   
  
  
Total Cost: 24109.7   
  
  
Query Degree: 1   
  
  
Rows RETURN ( 1) Cost I/O   
  
  
| 1 GRPBY ( 2) 24109.7 17002 | 1.2e+007 IXSCAN ( 3) 23259.5 17002 | 1.2e+007   
  
  
INDEX: ADMINISTRATOR   
  
  
IDX_NOPART_L_ORDERKEY Q1   
 
 

Listing 9 shows that when the index IDX_NOPART_L_ORDERKEY is used to calculate the total number of rows in Table T1, the estimated total cost is 24109.7, the IO count is estimated to be 17002, and the actual time spent is 2.45 seconds.

Listing 10. Create a partition Index

 
 

  
  
db2 "Create index idx_part_l_orderkey on t1(l_orderkey) partitioned"   
  
  
db2 "runstats on table db2inst1.t1 and indexes all"   
  
  
db2 "select substr(INDNAME,1,25) idx_name,DATAPARTITIONID,NLEVELS,NLEAF,INDCARD   
  
  
from syscat.indexpartitions"   
  
  
IDX_NAME DATAPARTITIONID NLEVELS NLEAF INDCARD   
  
  
IDX_PART_L_ORDERKEY 0 3 1134 1021133   
  
  
IDX_PART_L_ORDERKEY 1 3 1062 956131   
  
  
IDX_PART_L_ORDERKEY 2 3 1136 1023293   
  
  
IDX_PART_L_ORDERKEY 3 3 1098 988650   
  
  
IDX_PART_L_ORDERKEY 4 3 1134 1021552   
  
  
IDX_PART_L_ORDERKEY 5 3 1100 990715   
  
  
IDX_PART_L_ORDERKEY 6 3 1134 1020850   
  
  
IDX_PART_L_ORDERKEY 7 3 1137 1023727   
  
  
IDX_PART_L_ORDERKEY 8 3 1101 991839   
  
  
IDX_PART_L_ORDERKEY 9 3 1133 1020225   
  
  
IDX_PART_L_ORDERKEY 10 3 1078 970906   
  
  
IDX_PART_L_ORDERKEY 11 3 1078 970979   
 
 

Listing 10 shows that the partition index idx_part_l_orderkey has 12 partitions, and the B-tree height is three layers. In total, there are 13325 leaf pages, and the number of leaf pages is 20% less than that of non-partition indexes.

Listing 11. Test DB2 V9.7 partition index performance

 
 

  
  
db2 set current explain mode yes   
  
  
db2 values current timestamp   
  
  
1   
  
  
2009-07-07-15.59.09.722000   
  
  
db2 "select count(*) from t1 "   
  
  
1   
  
  
----------- 12000000   
  
  
db2 values current timestamp   
  
  
1   
  
  
2009-07-07-15.59.11.910000   
  
  
db2exfmt -d sample -w -1 -n % -s % -# 0 -t   
  
  
Total Cost: 24109.7   
  
  
Query Degree: 1 Total Cost: 22059.4   
  
  
Query Degree: 1   
  
  
Rows RETURN ( 1) Cost I/O | 1 GRPBY ( 2)   
  
  
22059.4 14178.4 | 1.2e+007 IXSCAN ( 3) 21209.2 14178.4 | 1.2e+007   
  
  
INDEX: ADMINISTRATOR   
  
  
IDX_PART_L_ORDERKEY Q1   
 
 

Listing 11 shows that when the index IDX_PART_L_ORDERKEY is used to calculate the total number of rows in Table T1, the estimated total cost is 22059.4, Which is 8.5% lower than that of non-partition indexes and 14178.4 lower than that of non-partition indexes, the actual time spent is 2.19 seconds, which is 10% lower than that of non-partitioned indexes.

The above tests show that the DB2 V9.7 partition index has certain performance advantages over the non-partition index in terms of space occupation and scanning performance.