DB2分区表创建及分区索引的创建

                一直用oracle数据库，近期刚接触DB2，最近用到DB2分区表，db2的资料太少了，下面总结下db2分区表的使用及本地索引的创建。

         db2分区表：使用原理跟oracle类似。最核心的是分区表会把表分区存储，在用到分区字段扫描数据时，只需要扫描数据所在分区的数据块，不需要全部扫描。两外删除更新时都很高效。

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

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

              一个分区表可以同时存在分区索引和非分区索引。如果创建分区索引，那个每个索引分区将都包含单个数据分区的索引条目，索引叶子节点中的 RID 也将只指向单个数据分区。一个分区表可以同时存在分区索引和非分区索引。如果创建分区索引，那个每个索引分区将都包含单个数据分区的索引条目，索引叶子节点中的 RID 也将只指向单个数据分区。

          分区表创建：

       
drop table DW_DEP_ACCT_DETAIL_HISTORY;commit;CREATE TABLE    DW_DEP_ACCT_DETAIL_HISTORY    (        DATA_DT VARCHAR(8) NOT NULL,        CYC VARCHAR(4) NOT NULL,        ORG_ID VARCHAR(8),        CUST_TYPE VARCHAR(8),        CS_NO VARCHAR(12),        CU_ID VARCHAR(30) NOT NULL,        ACCT_NAME VARCHAR(100),        AGE VARCHAR(4),        REGI_ADDR VARCHAR(200),        EDUCATION VARCHAR(8),        PRDT_ID VARCHAR(8),        ACCT_ID VARCHAR(30) NOT NULL,        BG_DT VARCHAR(8),        ED_DATE VARCHAR(8),        OPN_STAFF VARCHAR(10),        BAL DECIMAL(16,2),        LAST_YEAR_PERIOD_BAL DECIMAL(16,2),        DAY_AVG_BAL DECIMAL(16,2),        LAST_YEAR_AVG_BAL DECIMAL(16,2),        SURPLUS_TP VARCHAR(10),        STAFF_FL VARCHAR(8),        LOAN_CUS_FL VARCHAR(8),        DEP_LOAN_FL VARCHAR(8),        chsx    varchar(8) not null  ,        loan_bal DECIMAL(16,2),        PRIMARY KEY (CU_ID, ACCT_ID, DATA_DT, CYC,chsx) ) PARTITION BY RANGE(DATA_DT)( STARTING '20130601'  ,  STARTING '20130701' ENDING '20130731' ,  STARTING '20130801' ENDING '20130831' ,  STARTING '20130901' ENDING '20130930' ,  STARTING '20131001' ENDING '20131031' ,  STARTING '20131101' ENDING '20131130' ,  STARTING '20131201' ENDING '20131231' ,  STARTING '20140101' ENDING '20140131' ,  STARTING '20140201' ENDING '20140228' ,  STARTING '20140301' ENDING '20140331' ,  STARTING '20140401' ENDING '20140430' ,  STARTING '20140501' ENDING '20140531' ,  STARTING '20140601' ENDING '20140630' ,  STARTING '20140701' ENDING '20140731' ,  STARTING '20140801' ENDING '20140831' ,  STARTING '20140901' ENDING '20140930' ,  STARTING '20141001' ENDING '20141031' ,  STARTING '20141101' ENDING '20141130' ,  STARTING '20141201' ENDING '20141231' ,    STARTING '20150101' ENDING '20150131' ,  STARTING '20150201' ENDING '20150231' ,  STARTING '20150301' ENDING '20150331' ,  STARTING '20150401' ENDING '20150431' ,  STARTING '20150501' ENDING '20150531' ,  STARTING '20150601' ENDING '20150631' ,  STARTING '20150701' ENDING '20150731' ,  STARTING '20150801' ENDING '20150831' ,  STARTING '20150901' ENDING '20150931' ,  STARTING '20151001' ENDING '20151031' ,  STARTING '20151101' ENDING '20151131' ,  STARTING '20151201' ENDING '20151231' ,    STARTING '20160101' ENDING '20160131' ,  STARTING '20160201' ENDING '20160231' ,  STARTING '20160301' ENDING '20160331' ,    STARTING '20160401' ENDING '20160431' ,    STARTING '20160501' ENDING '20160531' ,  STARTING '20160601' ENDING '20160631' ,  STARTING '20160701' ENDING '20160731' ,  STARTING '20160801' ENDING '20160831' ,  STARTING '20160901' ENDING '20160931' ,  STARTING '20161001' ENDING '20161031' ,  STARTING '20161101' ENDING '20161131' ,  STARTING '20161201' ENDING '20161231' ,    STARTING '20170101' ENDING '20170131' ,  STARTING '20170201' ENDING '20170231' ,  STARTING '20170301' ENDING '20170331' ,  STARTING '20170401' ENDING '20170431' ,  STARTING '20170501' ENDING '20170531' ,  STARTING '20170601' ENDING '20170631' ,  STARTING '20170701' ENDING '20170731' ,  STARTING '20170801' ENDING '20170831' ,  STARTING '20170901' ENDING '20170931' ,  STARTING '20171001' ENDING '20171031' ,  STARTING '20171101' ENDING '20171131' ,  STARTING '20171201' ENDING '20171231' ,      STARTING '20180101' ENDING '20180131' ,   STARTING '20180201' ENDING '20180231' ,   STARTING '20180301' ENDING '20180331' ,   STARTING '20180401' ENDING '20180431' ,   STARTING '20180501' ENDING '20180531' ,   STARTING '20180601' ENDING '20180631' ,   STARTING '20180701' ENDING '20180731' ,   STARTING '20180801' ENDING '20180831' ,   STARTING '20180901' ENDING '20180931' ,   STARTING '20181001' ENDING '20181031' ,   STARTING '20181101' ENDING '20181131' ,   STARTING '20181201' ENDING '20181231' ,   STARTING '20190101' ENDING '20991231'   --ENDING in '20990101'   )   in DATATS1 index in  DATATS1 ;

        创建分区索引：Create index idx_parti_DW_DEP_ACCT_DETAIL_HISTORY_01 on DW_DEP_ACCT_DETAIL_HISTORY (DATA_DT) partitioned;

        创建非分区索引：Create index idx_parti_DW_DEP_ACCT_DETAIL_HISTORY_02 on DW_DEP_ACCT_DETAIL_HISTORY (DATA_DT) not partitioned;

        查看分区索引信息：

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='DW_DEP_ACCT_DETAIL_HISTORY' order by datapartitionid;

              查看表空间信息：

（1）、差可能分区表的表空间和索引表空

select tbspace,index_tbspace from syscat.tables where tabname='DW_DEP_ACCT_DETAIL_HISTORY'

发现表空间为空，怎么回事呢？

（2）、下面来看下单个分区的表空

select TBSPACEID from syscat.datapartitions where tabname='DW_DEP_ACCT_DETAIL_HISTORY' 

这里可以用db2 list tablespaces来查看Id为3的是哪个表空间。

发现：分区表每个分区都有单独一个表空间。也可以指定不同的表空间。

      所有db2系统表中分区表的表空间为空。但是索引表空间是固定的。

                  查看索引基本信息：

select tabname, INDNAME ,TBSPACEID ,colnames,a.* from syscat.indexes a where tabname='DW_DEP_ACCT_DETAIL_HISTORY' and indschema='FYDW';