簡介
在 DB2 V9.7 以前，范圍分區表只支持全局索引（或者稱為非分區索引），這樣即使分區表的數據是分布在多個表空間上的，分區表的所有索引只能存儲在同一個表空間中。這一特性限制了索引掃描只能在一個表空間上對該索引的頁面進行讀取，導致讀取頁面過多並約束了掃描的並行性。
DB2 V9.7 使用分區索引的索引組織方案，即索引數據根據表的分區方案分布到多個索引分區中，每個索引分區都只引用對應數據分區中的表行。從 DB2 V9.7 開始，創建索引時默認創建分區索引，除非出現以下情況：
對 CREATE INDEX 語句指定了 UNIQUE，並且索引鍵未包括所有表分區鍵列。
創建基於空間數據的索引。
創建基於 XML 數據的索引
分區索引的創建
一個分區表可以同時存在分區索引和非分區索引。如果創建分區索引，那個每個索引分區將都包含單個數據分區的索引條目，索引葉子節點中的 RID 也將只指向單個數據分區。
在我們使用 Alter table 語句的 Attach Partition 子句將數據 Roll in 或者 Roll Out 分區表時，分區索引將特別有用。如果使用非分區索引，那麼必須先發出 Set Integrity 語句，新添加的分區中的數據才能進入聯機狀態。這個過程可能非常消耗時間，並可能消耗大量日志空間。當使用分區索引時這些開銷將會被消除。
分區索引的每個分區均采用 B 樹結構存儲，由於分區後一個大型 B 樹被劃分為若干小型 B 樹，樹的層數將會減少，這會提高數據的插入、更新、刪除以及掃描的性能。同時我們執行查詢時，DB2 將會采用分區消除優化方法提高掃描性能和並行性。分區消除技術幫助優化器先過濾了不需要的索引分區，只需要掃描相應的分區就能完成查詢，這比掃描非分區的索引更為高效。
清單 1. 創建示例分區表與索引
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;
上面代碼中，我們創建了一個擁有四個分區的范圍分區表，同時創建了兩個索引。索引 index idx_t1_l_orderkey 是非分區索引，idx_t1_l_partkey 是分區索引。
我們下面查看一下上面代碼執行後數據字典關於表 T1 以及它的索引的描述。系統視圖 syscat.datapartitions 中包含了分區表的分區信息以及相關統計信息，系統視圖 syscat.indexpartitions 中則包含了分區索引的分區信息和相關統計信息。
清單 2. 分區表的數據分區信息
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'
清單 2 中代碼執行查詢，從系統視圖 syscat.datapartitions 中獲取關於數據分區的信息。查詢結果顯示，表 T1 具有四個分區，名稱與我們創建時相同，ID 依次為 0、1、2、3，數據分布在表空間 DMS_D1（ID 為 8）、DMS_D2（ID 為 9）、DMS_D2（ID 為 10）、DMS_D3（ID 為 11）上。本例中的表空間信息如下：
清單 3. 表空間信息
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
清單 4. 索引基本信息
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
我們看到非分區索引 IDX_T1_L_ORDERKEY 數據存放在表空間 DMS_D1 上（ID 為 8），這意味著在創建索引未明確指定表空間時，DB2 將使用第一個數據分區所在的表空間存放非分區索引。而索引 IDX_T1_L_PARTKEY 所在的表空間 ID 為 65530，我們通過表空間信息部分看到我們的數據庫中沒有 ID 為 65530 表空間。由於分區索引的存儲特性是和數據分區關聯的，一個索引將分布在多個表空間中，因此 DB2 用了 65530 特殊值表示索引的表空間，並不表示這個 ID 對應的表空間存在。
清單 5. 索引分區信息
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
我們看到索引 IDX_T1_L_PARTKEY 被分為 4 個區，存放索引的表空間與數據分區的表空間相同，如索引 IDX_T1_L_PARTKEY 第一個分區只引用數據分區 0（QUARTER01 分區）的行，數據保存在 DMS_D1 中。請注意索在創建分區索引時，不能直接為其指定用於存儲索引分區的表空間，其表空間由創建表時為數據分區指定的表空間確定。默認情況下，分區的缺省存放位置與它所引用的數據分區的位置相同。
清單 6. 為索引指定表空間
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;
上面的示例中，非分區索引 X1 存儲在表空間 TS2 上，這是由於在創建表 T1 的為所有非分區索引指定了缺省表空間 TS2 。
分區索引 X2 和 X3 的數據分區 0 對應的索引分區存儲在表空間 ts3 上，這是因為索引分區的缺省位置與其所引用的數據分區相同。數據分區 1、2 對應的索引分區存儲在表空間 ts4 上，這是因為這兩個數據分區明確指示了存儲索引的表空間。數據分區 4 對應的索引分區存儲存儲在 ts1 上，這是因為我們沒有給數據分區 4 指定存儲表空間，其數據默認存儲表空間為 ts1 。
在 DB2 V9.7 以後，用戶創建的索引默認都是分區索引。創建非唯一分區索引時將會 DB2 將會自動使用分區鍵進行分區。創建唯一分區索引時，索引列中必須包含用於分區的所有列，否則 DB2 將返回 SQL20303N 錯誤表示索引創建失敗。
分區索引空間占用與掃描性能
分區表主要應用在表比較大的場景下，因此我們使用大表才能測試出性能。下面我們創建測試表。
清單 7. 創建測試大表
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
我們創建的表包含 1200 萬行數據，按照月份每個月一個分區，分區列 l_shipdate 的數據分布在’ 2008-01-01 ’和’ 2008-12-31 ’之間，且均勻分布。注意 733042 是日期 2008-01-01 在 DB2 內以天數的表達形式，是通過 days() 函數獲得的。
我們首先在列 l_orderkey 上創建非分區索引。
清單 8. 創建非分區索引
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
清單 8 表明，非分區索引 B 樹高度為 3 層，具有 16831 個葉子頁面。
清單 9. 測試非分區索引性能
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
清單 9 表明使用索引 IDX_NOPART_L_ORDERKEY 統計表 T1 的總行數時，估計總成本為 24109.7，IO 次數估計為 17002，實際花費時間為 2.45 秒。
清單 10. 創建分區索引
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
清單 10 表明，分區索引 idx_part_l_orderkey 具有 12 個分區，B 樹高度為 3 層，合計具有 13325 個葉子頁面，葉子頁面數比非分區索引下降 20% 。
清單 11. 測試分區索引性能
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
清單 11 表明使用索引 IDX_PART_L_ORDERKEY 統計表 T1 的總行數時，估計總成本為 22059.4，比非分區索引下降 8.5%，IO 次數估計為 14178.4，比非分區索引下降 16%, 實際花費時間為 2.19 秒，比非分區索引下降 10% 。
上述測試表明，分區索引在空間占用、掃描性能方面比非分區索引具有一定的性能優勢。
分區索引對 Roll Out/Roll In 的影響
分區表的一個重要功能是能夠快速的將單個分區數據進行 Roll Out/Roll in 。在 DB2 V9.7 之前，對分區表進行 Roll Out/Roll in 時需要對所有索引進行維護，使用分區索引後，將會消除在 DETACH 時對索引進行的異步維護。在進行分區 Attach 時，只需要對新聯結上分區構建索引即可。與非分區索引相比，這兩個特性將會極大的提高分區 Roll In 速度，同時也會極大減少日志空間要求。照清單 7 創建測試大表的方法，生成 120 萬條記錄，時間為 2009-01-01 至 2009-01-31，不過需要注意日期 2009-01-01 在 DB2 內部表示的天數為為 733408 。我們准備把 2008-01-01 至 2008-01-31 數據 Detach 出來，然後將新生成的 2009-01-01 至 2009-01-31 數據 Attach 到 t1 表中。
清單 12. 非分區索引 Detach/Attach 性能
values current timestamp
1
--------------------------
2009-07-07-17.33.13.019000
alter table t1 DETACH PARTITION PART0 into temp
values current timestamp
1
--------------------------
2009-07-07-17.33.13.347000
load from d:/t10901.IXF of ixf replace into temp
values current timestamp
1
--------------------------
2009-07-07-17.33.31.722000
ALTER TABLE t1 ATTACH PARTITION PART0901 STARTING FROM '2009-01-01' ENDING AT '2
009-01-31' FROM temp
values current timestamp
1
--------------------------
2009-07-07-17.33.32.425000
COMMIT WORK
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 4162501098 35778902
values current timestamp
1
--------------------------
2009-07-07-17.33.32.519000
SET INTEGRITY FOR t1 ALLOW WRITE Access IMMEDIATE CHECKED
values current timestamp
1
--------------------------
2009-07-07-17.33.50.972000
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 3948641205 249638795
從清單 12 可以看出，DETACH 分區花費 0.34 秒，Attach 分區 0.7 秒，SET INTEGRITY 花費時間 18.453 秒。在 SET INTEGRITY 時，消耗日志 213859893 字節，約 203MB 。
清單 13. 分區索引 Detach/Attach 性能
values current timestamp
1
--------------------------
2009-07-07-17.27.44.753000
alter table t1 DETACH PARTITION PART0 into temp
values current timestamp
1
--------------------------
2009-07-07-17.27.45.128000
load from d:/t10901.IXF of ixf replace into temp
values current timestamp
1
--------------------------
2009-07-07-17.27.58.910000
ALTER TABLE t1 ATTACH PARTITION PART0901 STARTING FROM '2009-01-01' ENDING AT '2
009-01-31' FROM temp
values current timestamp
1
--------------------------
2009-07-07-17.27.59.738000
COMMIT WORK
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- --------------------
4163425378 34854622
values current timestamp
1
--------------------------
2009-07-07-17.27.59.863000
SET INTEGRITY FOR t1 ALLOW WRITE Access IMMEDIATE CHECKED
values current timestamp
1
--------------------------
2009-07-07-17.28.01.831000
select TOTAL_LOG_AVAILABLE ,TOTAL_LOG_USED from sysibmadm.snapdb
TOTAL_LOG_AVAILABLE TOTAL_LOG_USED
-------------------- -------------------- 4163421697 34858303
從清單 13 可以看出，DETACH 分區花費 0.375 秒，Attach 分區 0.828 秒，SET INTEGRITY 花費時間 1.97 秒。在 SET INTEGRITY 時，消耗日志 3681 字節。
從兩者對比可以看到，分區索引在聯結分區方面速度約提高了 10 倍，而日志空間消耗則減少了幾千倍。
總結
DB2 V9.7 提供的分區索引功能可以幫助我們更加快速的將數據 Roll in/Roll out 分區表，同時它在查詢優化方面能夠提供一定的性能幫助。分區索引在 DB2 V9.7 是默認行為，基本上不需要人工的參與，這又簡化了 DBA 的管理工作。
