以下的文章主要是闡述DB2 V9.7 本地分區索引的正確創建過程，我們大家都知道一個分區表可以同時存在分區索引與非分區索引。如果想創建分區索引，那個每個索引分區將都包含單個數據分區的索引條目，索引葉子節點中的 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_D1ID 為 8）、DMS_D2ID 為 9）、DMS_D2ID 為 10）、DMS_D3ID 為 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 TBSPACEIDT1 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 第一個分區只引用數據分區 0QUARTER01 分區）的行，數據保存在 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 錯誤表示索引創建失敗。
