软件开发培训班 >> 编程开发 >> DB2


DB2 V9.7 本地分区索引的正确创建过程描述

　　本文标签：DB2

　　以下的文章主要是阐述DB2 V9.7 本地分区索引的正确创建过程，我们大家都知道一个分区表可以同时存在分区索引与非分区索引  。如果想创建分区索引，那个每个索引分区将都包含单个数据分区的索引条目，索引叶子节点中的 RID 也将只指向单个数据分区  。

　　在我们使用 Alter table 语句的 Attach Partition 子句将数据 Roll in 或者 Roll Out 分区表时，分区索引将特别有用  。如果使用非分区索引，那么必须先发出 Set Integrity 语句，新添加的分区中的数据才能进入联机状态  。这个过程可能非常消耗时间，并可能消耗大量日志空间  。当使用分区索引时这些开销将会被消除  。

　　分区索引的每个分区均采用 B 树结构存储，由于分区后一个大型 B 树被划分为若干小型 B 树，树的层数将会减少，这会提高数据的插入、更新、删除以及扫描的性能  。同时我们执行查询时，DB2 将会采用分区消除优化方法提高扫描性能和并行性  。分区消除技术帮助优化器先过滤了不需要的索引分区，只需要扫描相应的分区就能完成查询，这比扫描非分区的索引更为高效  。

　　清单 1. 创建示例分区表与索引

　　

　　

  1. CREATE TABLE t1 ( l_orderkey INTEGER, l_partkey INTEGER, l_suppkey INTEGER,  
  2. l_shipdate date, padding1 char(30) )   
  3. PARTITION BY RANGE(l_shipdate)   
  4. (   
  5. partition quarter01 STARTING 2008-01-01 ENDING 2008-03-31in DMS_D1,   
  6. partition quarter02 STARTING 2008-04-01 ENDING 2008-06-30in DMS_D2,   
  7. partition quarter03 STARTING 2008-07-01 ENDING 2008-09-30 in DMS_D3,   
  8. partition quarter04 STARTING 2008-10-01 ENDING 2008-12-31 in DMS_D4   
  9. );   
  10. Create index idx_t1_l_orderkey on t1(l_orderkey) NOT partitioned ;   
  11. 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. 分区表的数据分区信息

　　

  1. db2 "select substr(TABNAME ,1,10) t_name,substr(DATAPARTITIONNAME ,1,10) part_name,   
  2. DATAPARTITIONID ,substr(LOWVALUE,1,12) low_value,substr(HIGHVALUE,1,12) high_value   
  3. from syscat.datapartitions where tabname=T1"   
  4. T_NAME PART_NAME DATAPARTITIONID TBSPACEID LOW_VALUE HIGH_VALUE ----------   
  5. T1 QUARTER01 0 8 2008-01-01 2008-03-31   
  6. T1 QUARTER02 1 9 2008-04-01 2008-06-30   
  7. T1 QUARTER03 2 10 2008-07-01 2008-09-30   
  8. 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. 表空间信息

　　

　　

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

　　清单 4. 索引基本信息

　　

  1. db2 "select substr(tabname,1,10) tab_name, substr(INDNAME,1,18) inx_name,TBSPACEID   
  2. from syscat.indexes where tabname=T1"   
  3. TAB_NAME INX_NAME TBSPACEIDT1 IDX_T1_L_ORDERKEY   
  4. 8   
  5. 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. 索引分区信息

　　

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

　　我们看到索引 IDX_T1_L_PARTKEY 被分为 4 个区，存放索引的表空间与数据分区的表空间相同，如索引 IDX_T1_L_PARTKEY 第一个分区只引用数据分区 0（QUARTER01 分区）的行，数据保存在 DMS_D1 中  。请注意索在创建分区索引时，不能直接为其指定用于存储索引分区的表空间，其表空间由创建表时为数据分区指定的表空间确定  。

　　默认情况下，分区的缺省存放位置与它所引用的数据分区的位置相同  。

　　

　　清单 6. 为索引指定表空间

　　

  1. CREATE TABLE t1 (columns) in ts1 INDEX IN ts2 1   
  2. PARTITION BY RANGE (column expression) (PARTITION PART0 STARTING   
  3. FROM constant ENDING constant IN ts3,   
  4. PARTITION PART1 STARTING FROM constant ENDING constant INDEX IN ts5,   
  5. PARTITION PART2 STARTING FROM constant ENDING constant INDEX IN ts4,   
  6. PARTITION PART3 STARTING FROM constant ENDING constant INDEX IN ts4,   
  7. PARTITION PART4 STARTING FROM constant ENDING constant)   
  8. CREATE INDEX x1 ON t1 (...) NOT PARTITIONED;   
  9. CREATE INDEX x2 ON t1 (...) PARTITIONED;   
  10. 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 错误表示索引创建失败  。