精华内容
下载资源
问答
  • 一、分区表 1.1 概念 Hive中的表对应为HDFS上的指定目录,在查询数据时候,默认会对全表进行扫描,这样时间性能的消耗都非常大。 分区为HDFS上表目录的子目录,数据按照分区存储在子目录中。如果查询的where...

    一、分区表

    1.1 概念

    Hive 中的表对应为 HDFS 上的指定目录,在查询数据时候,默认会对全表进行扫描,这样时间和性能的消耗都非常大。

    分区为 HDFS 上表目录的子目录,数据按照分区存储在子目录中。如果查询的 where 字句的中包含分区条件,则直接从该分区去查找,而不是扫描整个表目录,合理的分区设计可以极大提高查询速度和性能。

    这里说明一下分区表并 Hive 独有的概念,实际上这个概念非常常见。比如在我们常用的 Oracle 数据库中,当表中的数据量不断增大,查询数据的速度就会下降,这时也可以对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据存放到多个表空间(物理文件上),这样查询数据时,就不必要每次都扫描整张表,从而提升查询性能。

    1.2 使用场景

    通常,在管理大规模数据集的时候都需要进行分区,比如将日志文件按天进行分区,从而保证数据细粒度的划分,使得查询性能得到提升。

    1.3 创建分区表

    在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列,程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试:

     CREATE EXTERNAL TABLE emp_partition(
        empno INT,
        ename STRING,
        job STRING,
        mgr INT,
        hiredate TIMESTAMP,
        sal DECIMAL(7,2),
        comm DECIMAL(7,2)
        )
        PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
        ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
        LOCATION '/hive/emp_partition';
    

    1.4 加载数据到分区表

    加载数据到分区表时候必须要指定数据所处的分区:

    # 加载部门编号为20的数据到表中
    LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20)
    # 加载部门编号为30的数据到表中
    LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=30)
    

    1.5 查看分区目录

    这时候我们直接查看表目录,可以看到表目录下存在两个子目录,分别是 deptno=20deptno=30,这就是分区目录,分区目录下才是我们加载的数据文件。

    # hadoop fs -ls  hdfs://hadoop001:8020/hive/emp_partition/
    

    这时候当你的查询语句的 where 包含 deptno=20,则就去对应的分区目录下进行查找,而不用扫描全表。

    https://github.com/heibaiying

    二、分桶表

    1.1 简介

    分区提供了一个隔离数据和优化查询的可行方案,但是并非所有的数据集都可以形成合理的分区,分区的数量也不是越多越好,过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数,用来避免过多分区文件对文件系统产生负担。鉴于以上原因,Hive 还提供了一种更加细粒度的数据拆分方案:分桶表 (bucket Table)。

    分桶表会将指定列的值进行哈希散列,并对 bucket(桶数量)取余,然后存储到对应的 bucket(桶)中。

    1.2 理解分桶表

    单从概念上理解分桶表可能会比较晦涩,其实和分区一样,分桶这个概念同样不是 Hive 独有的,对于 Java 开发人员而言,这可能是一个每天都会用到的概念,因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。

    当调用 HashMap 的 put() 方法存储数据时,程序会先对 key 值调用 hashCode() 方法计算出 hashcode,然后对数组长度取模计算出 index,最后将数据存储在数组 index 位置的链表上,链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图:

    图片引用自:HashMap vs. Hashtable

    1.3 创建分桶表

    在 Hive 中,我们可以通过 CLUSTERED BY 指定分桶列,并通过 SORTED BY 指定桶中数据的排序参考列。下面为分桶表建表语句示例:

      CREATE EXTERNAL TABLE emp_bucket(
        empno INT,
        ename STRING,
        job STRING,
        mgr INT,
        hiredate TIMESTAMP,
        sal DECIMAL(7,2),
        comm DECIMAL(7,2),
        deptno INT)
        CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
        ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
        LOCATION '/hive/emp_bucket';
    

    1.4 加载数据到分桶表

    这里直接使用 Load 语句向分桶表加载数据,数据时可以加载成功的,但是数据并不会分桶。

    这是由于分桶的实质是对指定字段做了 hash 散列然后存放到对应文件中,这意味着向分桶表中插入数据是必然要通过 MapReduce,且 Reducer 的数量必须等于分桶的数量。由于以上原因,分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入,因为 CTAS 操作会触发 MapReduce。加载数据步骤如下:

    1. 设置强制分桶

    set hive.enforce.bucketing = true; --Hive 2.x 不需要这一步
    

    在 Hive 0.x and 1.x 版本,必须使用设置 hive.enforce.bucketing = true,表示强制分桶,允许程序根据表结构自动选择正确数量的 Reducer 和 cluster by column 来进行分桶。

    2. CTAS导入数据

    INSERT INTO TABLE emp_bucket SELECT *  FROM emp;  --这里的 emp 表就是一张普通的雇员表
    

    可以从执行日志看到 CTAS 触发 MapReduce 操作,且 Reducer 数量和建表时候指定 bucket 数量一致:

    https://github.com/heibaiying

    1.5 查看分桶文件

    bucket(桶) 本质上就是表目录下的具体文件:

    https://github.com/heibaiying

    三、分区表和分桶表结合使用

    分区表和分桶表的本质都是将数据按照不同粒度进行拆分,从而使得在查询时候不必扫描全表,只需要扫描对应的分区或分桶,从而提升查询效率。两者可以结合起来使用,从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例:

    CREATE TABLE page_view_bucketed(
    	viewTime INT, 
        userid BIGINT,
        page_url STRING, 
        referrer_url STRING,
        ip STRING )
     PARTITIONED BY(dt STRING)
     CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
     ROW FORMAT DELIMITED
       FIELDS TERMINATED BY '\001'
       COLLECTION ITEMS TERMINATED BY '\002'
       MAP KEYS TERMINATED BY '\003'
     STORED AS SEQUENCEFILE;
    

    此时导入数据时需要指定分区:

    INSERT OVERWRITE page_view_bucketed
    PARTITION (dt='2009-02-25')
    SELECT * FROM page_view WHERE dt='2009-02-25';
    

    参考资料

    1. LanguageManual DDL BucketedTables

    更多文章,欢迎访问 [全栈工程师手册] ,GitHub 地址:https://github.com/heibaiying/Full-Stack-Notes

    展开全文
  • 【对象管理】分区表

    千次阅读 2013-08-15 22:34:35
    进行分区后,逻辑上仍然是一张完整的,只是将中的数据在物理上存放到多个空间(物理文件上),这样查询数据时,不至于每次都扫描整张。 第一,改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,...
           当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。

    第一,改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。

    第二,增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;

    第三,维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;

    第四,均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。

     
    一、创建分区表

    1、创建范围分区表

    create table range_part 
    (time_id date,
     QUANTITY_SOLD NUMBER(10,2) ,
     AMOUNT_SOLD NUMBER(10,2)
    )partition by range(time_id)
    (
       partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
       partition part_1999 values less than (to_timestamp('2000-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
       partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
       partition part_next values less than (maxvalue)
    );----超过界限的处理:maxvalue

    2、创建列表分区

    create table list_part
    ( prod_id number,
      QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
      AMOUNT_SOLD    NUMBER(10,2)
    )partition by list(prod_id)
    (
       partition list_01 values(13,14,15,16,17),
       partition list_02 values(18,19,20,21,22),
       partition list_next values (default)
    );---超过界限的处理:default

    3、创建散列分区

       这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。

    create table hash_part
    ( prod_id number,
      QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
      AMOUNT_SOLD    NUMBER(10,2)
    )partition by hash(prod_id)
    (
        partition hash_01, 
        partition hash_02, 
        partition hash_03,
        partition hash_04
    );

    4、创建组合分区

         分区的三种类型(range,list,hash)可以根据需求两两组合,建立组合分区,基本语法差不多.

    create table range_hash_part
    ( prod_id number,
      time_id date,
      QUANTITY_SOLD  NUMBER(10,2),                                                                                                                                                                                  
      AMOUNT_SOLD    NUMBER(10,2)
    )partition by range(time_id) subpartition by list(prod_id)
    (
        partition range_01 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
        (
           subpartition range_01_list_01 values(13,14,15,16,17),
           subpartition range_01_list_02 values(18,19,20,21,22),
           subpartition range_01_list_03 values(default)
        ),
        partition range_02 values less than(maxvalue)
        (
           subpartition range_02_list_01 values(13,14,15,16,17),
           subpartition range_02_list_02 values(18,19,20,21,22),
           subpartition range_02_list_03 values(default)
        )
    );

    二、管理分区
    第一:添加分区 

        需要注意的是,所添加的分区分区界限必须要比最后一个分区界限要高。如下,由于最后一个分区的分区界限为maxvalue也就是最大值,所以不能再直接添加分区。

    alter table range_hash_part
    add
        partition range_03 values less than (to_timestamp('2000-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'))
        (
           subpartition range_03_list_01 values(13,14,15,16,17),
           subpartition range_03_list_02 values(18,19,20,21,22),
           subpartition range_03_list_03 values(default)
        )
    错误报告:
    SQL 错误: ORA-14074: 分区界限必须调整为高于最后一个分区界限
    14074. 00000 -  "partition bound must collate higher than that of the last partition"
    *Cause:    Partition bound specified in ALTER TABLE ADD PARTITION
               statement did not collate higher than that of the table's
               last partition, which is illegal.
    *Action:   Ensure that the partition bound of the partition to be added
               collates higher than that of the table's last partition.

        如下,我往散列分区中添加了一个新的分区,则没有上面的约束,因为散列分区没有最大值可言。

    alter table hash_part
    add partition hash_05;

    第二,删除分区

    SQL>alter table list_part drop partition list_next;

        如果表中只有一个分区的话,则不能删除,只能删除表。否则会报错。如下:

    alter table list_part drop partition list_01
    错误报告:
    SQL 错误: ORA-14083: 无法删除分区表的唯一分区
    14083. 00000 -  "cannot drop the only partition of a partitioned table"
    *Cause:    A drop partition command is being executed when there
               is only one partition in the table
    *Action:   Ensure that there is at least one partition.
               Drop table to remove all partitions

    第三、截断分区

    截断子分区:

    SQL>alter table range_hash_part truncate subpartition range_01_list_03;

    截断分区:

    SQL>alter table list_part truncate partition list_01;

    第四、合并分区 

       合并分区的时候,合并的分区必须是相邻的,并且结果分区将采用较高的分区,不能合并到界限较低的分区。如下面:

    alter table range_part 
    merge partitions part_1998,part_next into partition part_next;
    错误报告:
    SQL 错误: ORA-14274: 要合并的分区不相邻
    14274. 00000 -  "partitions being merged are not adjacent"
    
    alter table range_part 
    merge partitions part_1998,part_1999 into partition part_1998
    错误报告:
    SQL 错误: ORA-14275: 不能将下界分区作为结果分区重用
    14275. 00000 -  "cannot reuse lower-bound partition as resulting partition"
         不过在合并子分区的时候,不相邻的也能合并。

    alter table range_hash_part  
    merge subpartitions range_01_list_01 ,range_01_list_03  into subpartition range_01_list_03 ;
          另外,不能对散列分区表进行合并操作。           
    SQL> ALTER TABLE hash_part
      2  merge partitions hash_01,hash_02 into partition hash_02;
    ALTER TABLE hash_part
                *
    ERROR at line 1:
    ORA-14255: table is not partitioned by Range, List, Composite Range or
    Composite List method
    第五、拆分分区
                

            拆分分区是跟合并分区相反的操作,因此也不适合对HASH分区使用,对于HASH 分区添加一个分区即可。通常我们会用来拆分MAXVALUE/DEFAULT分区。下面例子中,符合要求的分区数据会被分割到第一个分区,其他的被分在另外的一个分区。

    SQL> alter table range_hash_part
      2  split subpartition range_02_list_02 values (18,19) into
      3  (subpartition range_02_list_05 ,
      4   subpartition range_02_list_04  );
    
    Table altered.
    第六、移动分区,可以移动分区的数据到别的表空间中

    SQL>Alter table tab move partition p_tab tablespace tbl update indexes;
    -----加上update indexes可以避免数据的移动使得索引无效
    第七、修改分区

     修改LIST 分区:

    SQL> alter table list_part
      2  modify partition list_01  add values(18,19);---添加分区值
    
    Table altered.
    
    SQL> alter table list_part
      2  modify partition list_01 drop values(18,19);---删除分区值
    
    Table altered.
    第八、交换分区

    SQL>alter table range_hash_part exchange subpartition range_01_list_03 with table tab;

            交换分区的时候,需要注意的是交换的表必须不是分区表、聚簇表。否则会报错。下面的SALES是分区表,然后就报错了。

    SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table sales;
    alter table range_hash_part exchange subpartition range_01_list_03 with table sales
                                                                                  *
    ERROR at line 1:
    ORA-14276: EXCHANGE SUBPARTITION requires a non-partitioned, non-clustered
    table
              另外,交换分区的两个表的结构要一样,否则会报错。

    SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table tab;
    alter table range_hash_part exchange subpartition range_01_list_03 with table tab
                                                                                  *
    ERROR at line 1:
    ORA-14277: tables in EXCHANGE SUBPARTITION must have the same number of columns
             还有就是,交换分区的表的数据要符合分区表的约束要求,否则也会报错。

    SQL> alter table range_hash_part exchange subpartition range_01_list_03 with table tab;
    alter table range_hash_part exchange subpartition range_01_list_03 with table tab
                                                                                  *
    ERROR at line 1:
    ORA-14280: all rows in table do not qualify for specified subpartition
    第九、接合分区,也就是散列分区的合并

          当散列分区表中中某个分区的数据量比较大的时候,可以多添加几个散列分区,然后采用下面的结合语法,对所有的散列分区数据进行平衡。

    SQL> ALTER TABLE hash_part coalesce PARTITION;
    
    Table altered.

    三、创建分区索引

         分区索引有三种,一种是普通的索引,索引的范围是所有分区;一种是本地分区索引,索引的范围是单独的分区;还有一种是全局分区索引,索引的范围是指定的分区。具体的区别可以查看下面的图片。


    1、创建普通索引

    SQL> create index part_ind on range_hash_part (prod_id);
    
    Index created.
    2、创建本地分区索引

    SQL> create index part_ind on range_hash_part (prod_id) local ;
    
    Index created.

         创建本地分区索引的时候,针对的是所有的分区,不能单独指定分区,否则会报语法错误。如下:

    SQL> create index part_ind on range_part(time_id)
      2  local
      3  (
      4     partition part_1998 ,
      5     partition part_2000
      6  );
    create index part_ind on range_part(time_id)
                             *
    ERROR at line 1:
    ORA-14024: number of partitions of LOCAL index must equal that of the
    underlying table

    3、创建全局分区索引

    SQL> create index part_ind on range_part(time_id)
      2  global partition by range(time_id)
      3  (
      4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')) ,
      5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
      6     partition part_next values less than (maxvalue)
      7  );
    
    Index created.

           需要注意的是,对于范围分区索引来说,创建全局分区索引必须要带有maxvalue的分区,否则会报下面的错误:

    SQL> create index part_ind on range_part(time_id)
      2  global partition by range(time_id)
      3  (
      4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')) ,
      5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS'))
      6  );
    )
    *
    ERROR at line 6:
    ORA-14021: MAXVALUE must be specified for all columns

           还有就是,全局分区索引只能是prefix index。所谓prefix index也就是第一个index key必须是partition key为开头的第一列。假设索引列为(prod_id,time_id),而分区是依据time_id分区的,则创建(prod_id,time_id)的索引列是错误的,必须是time_id开头,也就是(time_id,prod_id)或者直接创建索引(time_id),其他的如(prod_id)都会报错。如下:

    SQL> create index part_ind on range_part(QUANTITY_SOLD)
      2  global partition by range(time_id)
      3  (
      4     partition part_1998 values less than (to_timestamp('1999-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS')),
      5     partition part_2000 values less than (to_timestamp('2001-01-01 00:00:00','YYYY-MM-DD HH24:Mi:SS'))
      6  );
    global partition by range(time_id)
                                     *
    ERROR at line 2:
    ORA-14038: GLOBAL partitioned index must be prefixed
          不过,本地分区索引则无所谓,prefixed index和non-prefixed index都支持。

    四、查看分区信息

    1、查看一个用户有多少分区表,以及判断一个表是不是分区表:

    SQL>select * from user_tables  where partitioned='YES'

    2、查看一个用户拥有的分区表SUMMARY信息:

    SQL>select * from USER_PART_TABLES WHERE table_name ='RANGE_HASH_PART';

    3、查看一个表有多少分区,以及显示分区相关的统计信息:

    SQL>select * from user_tab_partitions where table_name='RANGE_HASH_PART';
    

    4、查看一个表有多少子分区,以及显示子分区相关的统计信息:

    SQL>select * from user_tab_subpartitions where table_name='RANGE_HASH_PART';
    

    5、查看一个表的分区列是什么:

    SQL>select * from USER_PART_KEY_COLUMNS WHERE NAME ='RANGE_HASH_PART';

    6、查看一个表的子分区列是什么:

    SQL>select * from user_SUBPART_KEY_COLUMNS WHERE NAME ='RANGE_HASH_PART';


    展开全文
  • mysql按月分区: PARTITION s201612 VALUES LESS THAN (TO_DAYS('2017-01-01 ')), PARTITION s201701 VALUES LESS THAN (TO_DAYS('2017-02-01')), PARTITION s201702 VALUES LESS THAN (TO_DAYS('2017-03-01 ')), ...
  • 我将在分区表和分区表之间进行性能测试。 这里有两个表都包含2亿个数据。 1)hashvalue_pt(每月分区表) 2)哈希(非Partiton表) PS请访问PostgreSQL中的分区表(创建分区)–第1部分。如果您不知道如何...

    我将在分区表和非分区表之间进行性能测试。

    这里有两个表都包含2亿个数据。

    1)hashvalue_pt(每月分区表)
    2)哈希值(非Partiton表)

    PS请访问PostgreSQL中的分区表(创建分区)–第1部分。如果您不知道如何在PostgreSQL中进行表分区,请访问PostgreSQL中的分区表(模拟数百万数据)–第2部分,以模拟数百万个数据进行测试。

    指定日期的性能测试

    
    --partition table
    SELECT * FROM hashvalue_PT WHERE hashtime = DATE '2008-08-01'
    
    --non partition table
    SELECT * FROM hashvalue WHERE hashtime = DATE '2008-08-01'
    

    这是从100万到2亿个数据的性能测试结果。 请点击图片放大性能结果。

    分区表性能测试PostgreSQL 1

    当两者都包含2亿个数据时,在指定日期进行搜索,分区表要比非分区表快144.45%

    在指定的日期搜索“ 2008-08-01”
    检索记录= 741825
    分区表= 359.61秒
    非分区表= 879.062秒

    范围日期同月的性能测试

    
    --partition table
    SELECT * FROM hashvalue_PT WHERE  hashtime >= DATE '2008-05-01' AND hashtime <= '2008-05-15'; 
    
    --non partition table
    SELECT * FROM hashvalue WHERE hashtime >= DATE '2008-05-01' AND hashtime <= '2008-05-15'; 
    

    这是从100万到2亿个数据的性能测试结果。 请点击图片放大性能结果。

    分区表性能测试PostgreSQL 2

    当两者都包含2亿个数据时,在同月的范围日期中进行搜索,分区表比非分区表要快约209.84%

    搜索从“ 2008-05-01”到“ 2008-05-15”的当月范围日期;
    检索记录= 11112338
    分区表= 603.328秒
    非分区表= 1869.375秒

    范围日期跨月性能测试

    
    --partition table
    SELECT * FROM hashvalue_PT WHERE  hashtime >= DATE '2008-06-20' AND hashtime <= '2008-07-10'; 
    
    --non partition table
    SELECT * FROM hashvalue WHERE hashtime >= DATE '2008-06-20' AND hashtime <= '2008-07-10';
    

    这是从100万到2亿个数据的性能测试结果。 请点击图片放大性能结果。

    分区表性能测试PostgreSQL 3

    当两者都包含2亿个数据时,在范围日期和跨月搜索中,分区表要比非分区表快39.97%

    搜索范围日期和跨月范围从“ 2008-06-20”到“ 2008-07-10”;
    检索记录= 12220658
    分区表= 1310.766秒
    非分区表= 1834.743秒

    结论

    经过分区和非分区表之间的一些性能测试后,结果显然是,分区表在该性能测试中完全获胜。

    翻译自: https://mkyong.com/database/performance-testing-on-partition-table-in-postgresql-part-3/

    展开全文
  • 首先我在Hbase中建了一张空表t1; 在hive中新建一张**外部分区表**使用HBaseStorageHandler与表...所以分区表和映射表同时使用无意义??![图片说明](https://img-ask.csdn.net/upload/201611/16/1479264006_91643.png)
  • DB2 9表分区

    2019-12-15 22:22:36
    DB2 9表分区 开始之前 CREATE TABLE 语句的 PARTITION BY 子句指定了数据的分区。该定义中使用的列被称为表分区键列。...数据可轻易实现转入转出 对大型的管理更加轻松 灵活的索引放置 更高的业务智...

    DB2 9表分区

    开始之前

    CREATE TABLE 语句的 PARTITION BY 子句指定了表数据的分区。该定义中使用的列被称为表分区键列。

    关于该特性的详细说明可参见 “Table partitioning in DB2 9”(developerWorks,2006 年 5 月)。

    表分区特性提供以下收益:

    • 表数据可轻易实现转入和转出
    • 对大型表的管理更加轻松
    • 灵活的索引放置
    • 更高的业务智能样式查询的性能

    关于本教程

    本教程中的练习将引领您使用表分区特性,并演示了表数据的转入转出、更轻松的大型表管理、灵活的索引放置和对业务智能样式查询的性能改善。

    这些练习旨在演示上述各领域内的一个或多个任务。

    目标

    本教程的目标是在以下方面探究 DB2 9 范围分区的特性和优点:

    • 创建范围分区表

    • 分区的转入和转出

    • 分区表的管理

    • 索引管理和放置

    先决条件

    本教程的目标读者是那些技能和经验刚刚迈入中级水平的 DB2 专业人员。要学习本教程,您应该熟悉 DB2 命令行、DB2 管理工具的使用,还应具备 SQL 实践经验。

    系统需求

    要运行本教程的示例,需要具备以下条件:

    • DB2 9 Data Server
    • Microsoft® Windows® 2000 或更高版本,以及一个具有管理员权限的帐户,或具有根访问权限的 Linux®(验证版)。
    • 确保系统中的 Java Runtime Environment 是 1.4.2 或更高版本。
    • 参考 DB2 9 系统需求页面 确保您的硬件符合要求。

    可通过上面的链接获取 DB2 9 Express C。关于安装 DB2 的步骤请参考 “DB2 XML 评估指南”(developerWorks,2006 年 6 月)。若未改动 DB2 的配置,安装后 DB2 将自动启动。

    使用 partition.zip 文件提供的示例脚本和数据演示本教程的概念。将其内容解压缩到 scripts 子目录(C:\scripts 或 home/userid/scripts)。本教程中将该目录简称为 stmm_scripts。教程假设您使用的是 DB2 默认安装目录,并且所有的 DB2 练习都通过一个数据库管理员 ID 执行。

    创建分区表

    这个实验将探讨创建分区表、将数据载入分区表以及使用 describe 命令来说明表范围的方法:

    1. 您将登录并为所有的练习设置基本环境。
    2. 您将创建不同格式的分区表并加载数据。
    3. 您将使用 DB2 命令和 SQL 查看结果。
    4. 将对 DB2 9 表范围分区进行概述。

    登录和基本指令

    图 1. 基本设置

    命令窗口

    1. 登录到您的机器,如图 1 所示,使用 db2inst1。

    2. 打开终端窗口(Linux)或 DB2 命令窗口(windows)。

    3. 切换到 scripts 子目录。

      清单 1. 切换目录

      cd c:\scripts

    4. 使用db2start 命令启动 DB2,并连接到 SAMPLE 数据库。

      清单 2. StartDB2
      db2start
      db2 connect to SAMPLE
      

    创建基本分区表

    这一节将介绍分区表的基本创建和加载。您将创建不同格式的表、验证创建结果、加载数据并对表进行查询。

    1. 使用如下的数据定义语言(DDL)创建具有四个范围的 LINEITEM 表:

    清单 3. 创建表

    CREATE TABLE LINEITEM 
    ( l_orderkey         DECIMAL(10,0) NOT NULL,
      l_partkey          INTEGER,
      l_suppkey          INTEGER,
      l_linenumber       INTEGER,
      l_quantity         DECIMAL(12,2),
      l_extendedprice    DECIMAL(12,2),
      l_discount         DECIMAL(12,2),
      l_tax              DECIMAL(12,2),
      l_returnflag       CHAR(1),
      l_linestatus       CHAR(1),
      l_shipdate         DATE,
      l_commitdate       DATE,
      l_receiptdate      DATE,
      l_shipinstruct     CHAR(25),
      l_shipmode         CHAR(10),
      l_comment          VARCHAR(44))
      PARTITION BY RANGE(l_shipdate)
    ( STARTING '1/1/1992' ENDING '30/06/1992',
      STARTING '1/7/1992' ENDING '31/12/1992',
      STARTING '1/1/1993' ENDING '30/6/1993',
      STARTING '1/7/1993' ENDING '31/12/1993')
    
    1. 创建该表的 SQL 语句位于 EX1-6.sql 文件中,可使用如下命令运行该文件:

      清单 4. 运行 EX1-6

      db2 –vtf EX1-6.sql

    2. 使用下面的命令说明为 LINEITEM 表创建的分区的范围:

      清单 5. 说明

      db2 describe data partitions for table LINEITEM

      图 2. 说明为 LINEITEM 表创建的分区范围

    命令窗口

    1. 注意:创建了四个数据分区。其中的范围包括边界值。

    2. 将数据导入到 LINEITEM 表。该操作的导入命令位于 EX1-8.sql 文件中,可使用如下命令运行:

      清单 6. 带有拒绝的加载

      db2 –vtf EX1-8.sql

      图 3. 将数据导入到 LINEITEM 表

    命令窗口

    1. 注意:导入时拒绝了 729 行数据,这是因为它们不具有位于当前 LINEITEM 表的数据分区定义范围内的 l_shipdate。

    2. 标量函数可用于显示行所属的数据分区号(datapartitionnum)。执行以下示例 SQL 查看标量函数的输出:

      清单 7. 查询 - 匹配日期的分区
    db2 “select datapartitionnum(l_shipdate) as PartitionId, l_shipdate from lineitem 
            where l_shipdate between01/06/1992and31/07/1992order by l_shipdate”
    
    图 4. 标量函数的输出

    命令窗口

    1. 注意:标量函数(datapartitionnum)返回的值和 describe 命令返回的是同一个 PartitionId。该语句的子句间使用的谓词范围超出了 PartitionId 0 和 PartitionId 1 的边界

    具有全部范围的分区表

    1. 创建具有两个额外数据分区的新 LINEITEM 表,其中一个分区用来捕获低于当前范围的值,另一个分区用来捕获高于当前范围的值。首先使用下面的命令删除现有的 LINEITEM 分区表:

      清单 8. 删除表

      db2 drop TABLE LINEITEM

    然后使用如下 DDL 创建 LINEITEM 表的新版本:

    清单 9. 创建表
    CREATE TABLE LINEITEM 
    ( l_orderkey         DECIMAL(10,0) NOT NULL,
      l_partkey          INTEGER,
      l_suppkey          INTEGER,
      l_linenumber       INTEGER,
      l_quantity         DECIMAL(12,2),
      l_extendedprice    DECIMAL(12,2),
      l_discount         DECIMAL(12,2),
      l_tax              DECIMAL(12,2),
      l_returnflag       CHAR(1),
      l_linestatus       CHAR(1),
      l_shipdate         DATE,
      l_commitdate       DATE,
      l_receiptdate      DATE,
      l_shipinstruct     CHAR(25),
      l_shipmode         CHAR(10),
      l_comment          VARCHAR(44))
      PARTITION BY RANGE(l_shipdate)
    ( STARTING MINVALUE,
      STARTING '1/1/1992' ENDING '30/06/1992',
      STARTING '1/7/1992' ENDING '31/12/1992',
      STARTING '1/1/1993' ENDING '30/6/1993',
      STARTING '1/7/1993' ENDING '31/12/1993',
      ENDING MAXVALUE)
    
    1. 创建该表的 SQL 位于 EX1-10.sql 文件中,可使用下列命令运行:

      清单 10. 运行 EX1-10

      db2 –vtf EX1-10.sql

    2. 使用下面的命令说明为 LINEITEM 表创建的分区范围。

      清单 11. 说明

      db2 describe data partitions for table LINEITEM

      图 5. 说明为 LINEITEM 表创建的分区范围

    命令窗口

    1. 注意:新的 MINVALUE 范围具有一个最高值,该值和下一个数据分区开始部分的值相等,但它并不包含该值。MAXVALUE 范围具有一个最低值,该值和前一个范围结束部分的值相等,但它不包含该值。这将创建一个无间隙的连续范围。

    2. 将数据导入到 LINEITEM 表中。该操作的导入命令位于 EX1-8.sql 文件中,可使用下面的命令运行该文件:

      清单 12. 全面加载

      db2 –vtf EX1-8.sql

      图 6. 将数据导入到 LINEITEM 表中

    命令窗口

    具有生成范围的分区表

    1. 创建一个新 LINEITEM 表,它具有从 1992 年 1 月 1 日到 1998 年 12 月 31 日按月划分的数据分区生成范围。同样,添加 minvalue 和 maxvalue 范围来存放具有超过此范围的 l_shipdate 的值的行。首先使用以下命令删除现有的 LINEITEM 分区表:

      清单 13. 删除表

      db2 drop TABLE LINEITEM

      然后使用如下 DDL 创建 LINEITEM 表的新版本:

      清单 14. 创建表
    CREATE TABLE lineitem
    (l_orderkey            DECIMAL(10,0) NOT NULL,
      l_cpartkey      INTEGER,
      l_suppkey       INTEGER,
      l_linenumber    INTEGER,
      l_quantity      DECIMAL(12,2),
      l_extendedprice DECIMAL(12,2),
      l_discount      DECIMAL(12,2),
      l_tax           DECIMAL(12,2),
      l_returnflag    CHAR(1),
      l_linestatus    CHAR(1),
      l_shipdate      DATE,
      l_commitdate    DATE,
      l_receiptdate   DATE,
      l_shipinstruct  CHAR(25),
      l_shipmode      CHAR(10),
      l_comment       VARCHAR(44))
      PARTITION BY RANGE(l_shipdate)
     (STARTING MINVALUE,
     STARTING '1/1/1992' ENDING '31/12/1998' 
                    EVERY 1 MONTH,
     ENDING MAXVALUE);
    
    1. 创建该表的 SQL 位于 EX1-13.sql 文件中,可使用下面的命令运行该文件:

      清单 15. 运行 EX1-13

      db2 –vtf EX1-13.sql

    2. 使用如下命令来说明为 LINEITEM 表创建的分区范围:

      清单 16. 说明

      db2 describe data partitions for table LINEITEM

      图 7. 说明为 LINEITEM 表创建的分区范围

    命令窗口

    1. 注意:创建了 86 个数据分区,但是没有包括这些范围的最高值,因为这些最高值将和之后的数据分区的最低值重叠。

    2. 将数据导入到 LINEITEM 表。此操作的导入命令位于 EX1-8.sql 文件中,可使用下面的命令运行该文件:

      清单 17. 加载并生成

      db2 –vtf EX1-8.sql

      图 8. 将数据导入到 LINEITEM 表

    命令窗口

    使用下面的 SQL 来验证 LINEITEM 表每一个数据分区的行数:

    清单 18. 查询数据
    db2 “select year(l_shipdate) as year, month(l_shipdate) as month, 
    count(*) as count from lineitem
      group by year(l_shipdate), month(l_shipdate)
      order by 1, 2

    使用以下的 SQL 语句验证 LINEITEM 表的每一个数据分区的行数:

    清单 19. 查询数据脚本
    db2 –vtf EX1-16.sql
    
    图 9. 检验行数

    命令窗口

    1. 注意:执行加载操作后,86 个范围中有 82 个范围包含一个或多个行。

    放置分区表

    该实验探讨放置分区表以及使用 describe 命令说明表内的范围和放置情况的方法:

    1. 您将为数据放置练习创建新的表空间。
    2. 你将创建不同格式的分区表。
    3. 您将使用 db2 命令和 SQL 查看结果。

    基本环境设置

    1. 使用

      describe data partitions
      

      命令以及

      show detail
      

      方法来显示表空间的分区放置。

      清单 20. 创建表

      db2 describe data partitions for table LINEITEM show detail

      图 10. 表空间的分区放置

    命令窗口

    1. 注意:TableSpID 列给出了包含分区的表空间的 ID 号。在本例中,TableSpID 是 ‘3’。

    2. 使用

      list tablespaces
      

      命令标识与 TableSpId 相关联的表空间。

      清单 21. 说明

      db2 list tablespaces

      图 11. 标识表空间

    命令窗口

    1. 注意:相应值为 ‘2’ 的 TableSpID 是 USERSPACE1 或默认的表空间。

    2. 现在将创建五个表空间来说明不同的放置选项。使用如下命令:

      清单 22. 说明
    db2 create tablespace dms_d1 managed by database using (file 'c:\ts1' 10000);
    db2 create tablespace dms_d2 managed by database using (file 'c:\ts2' 10000);
    db2 create tablespace dms_d3 managed by database using (file 'c:\ts3' 10000);
    db2 create tablespace dms_d4 managed by database using (file 'c:\ts4' 10000);
    db2 create tablespace dms_i1 managed by database using (file 'c:\ts5' 10000);
    
    1. 创建该表空间的 SQL 位于 EX2-3.sql 文件中,可使用下面的命令运行该文件:

      清单 23. 查询数据脚本

      db2 –vtf EX2-3.sql

    2. 创建一个新的 LINEITEM 表,具有位于 dms_d1 和 dms_d2 表空间的生成分区集。首先,使用如下命令删除现有的 LINEITEM 分区表:

      清单 24. 删除表

      db2 drop TABLE LINEITEM

      然后,使用下面的 DDL 创建 LINEITEM 表的新版本:

      清单 25. 创建表
    CREATE TABLE LINEITEM
    (l_orderkey      DECIMAL(10,0) NOT NULL,
     l_partkey       INTEGER,
     l_suppkey       INTEGER,
     l_linenumber    INTEGER,
     l_quantity      DECIMAL(12,2),
     l_extendedprice DECIMAL(12,2),
     l_discount      DECIMAL(12,2),
     l_tax           DECIMAL(12,2),
     l_returnflag    CHAR(1),
     l_linestatus    CHAR(1),
     l_shipdate      DATE,
     l_commitdate    DATE,
     l_receiptdate   DATE,
     l_shipinstruct  CHAR(25),
     l_shipmode      CHAR(10),
     l_comment       VARCHAR(44))
     IN DMS_D1, DMS_D2
     PARTITION BY RANGE(l_shipdate)
    (STARTING MINVALUE,
     STARTING '1/1/1992' 
          ENDING '31/12/1998' EVERY 1 MONTH,
     ENDING MAXVALUE);
    
    1. 创建该表的 SQL 位于 EX2-4.sql 文件中,可使用下面的命令运行该文件:

      清单 26. 运行 EX2-4

      db2 –vtf EX2-4.sql

    2. 使用下面的命令说明为 LINEITEM 表创建的分区范围:

      清单 27. 说明

      db2 describe data partitions for table LINEITEM show detail

      图 12. 说明为 LINEITEM 表创建的分区范围

    命令窗口

    图 13. 分区

    命令窗口

    1. 注意:TableSpID 列给出了包含分区的表空间的 ID 号。在本例中,TableSpID 为 4(对应于 DMS_D1)或 5(对应于 DMS_D2)。本例中将生成的分区依次分配给指定的表空间。

    分区的显式放置

    1. 创建一个具有四个数据分区的新 LINEITEM 表,每一个数据分区被显式地放在表空间中。首先使用如下命令删除现有的 LINEITEM 表:

      清单 28. 删除表

      db2 drop TABLE LINEITEM

      然后使用下面的 DDL 创建 LINEITEM 表的新版本:

      清单 29. 创建表
    CREATE TABLE LINEITEM
     (l_orderkey          DECIMAL(10,0) NOT NULL,
      l_partkey           INTEGER,
      l_suppkey           INTEGER,
      l_linenumber        INTEGER,
      l_quantity          DECIMAL(12,2),
      l_extendedprice     DECIMAL(12,2),
      l_discount          DECIMAL(12,2),
      l_tax               DECIMAL(12,2),
      l_returnflag        CHAR(1),
      l_linestatus        CHAR(1),
      l_shipdate          DATE,
      l_commitdate        DATE,
      l_receiptdate       DATE,
      l_shipinstruct      CHAR(25),
      l_shipmode          CHAR(10),
      l_comment           VARCHAR(44))
     PARTITION BY RANGE(l_shipdate)
     ( STARTING MINVALUE IN DMS_D1,
       STARTING '1/1/1992' ENDING '31/12/1992' IN DMS_D2,
       STARTING '1/1/1993' ENDING '31/12/1993' IN DMS_D3,
       ENDING MAXVALUE IN DMS_D4 );
    
    1. 创建表的 SQL 位于 EX2-6.sql 文件中,可使用下面的命令运行该文件:

      清单 30. 运行 EX2-6

      db2 –vtf EX2-6.sql

    2. 使用下面的命令说明为 LINEITEM 表创建的分区范围:

      清单 31. 说明

      db2 describe data partitions for table LINEITEM show detail

      图 14. 说明为 LINEITEM 表创建的分区范围

    命令窗口

    1. 注意:在本例中,每一个分区被放置在一个不同的 TableSpID 中,这个 TableSpID 和创建表的 DDL 中指定的表空间是相对应的。

    2. 创建一个具有四个数据分区的 LINEITEM 表,每一个数据分区被显式地放在表空间并且索引被放在表空间 DMS_I1 中。 在这一步中,将引入命名分区的概念,而不是使用默认的生成名称。 首先,使用下面的命令删除现有的 LINEITEM 分区表:

      清单 32. 删除表

      db2 drop TABLE LINEITEM

      然后,使用以下的 DDL 创建 LINEITEM 表的新版本:

      清单 33. 创建表
    CREATE TABLE LINEITEM
    (l_orderkey           DECIMAL(10,0) NOT NULL,
     l_partkey            INTEGER,
     l_suppkey            INTEGER,
     l_linenumber         INTEGER,
     l_quantity           DECIMAL(12,2),
     l_extendedprice      DECIMAL(12,2),
     l_discount           DECIMAL(12,2),
     l_tax                DECIMAL(12,2),
     l_returnflag         CHAR(1),
     l_linestatus         CHAR(1),
     l_shipdate           DATE,
     l_commitdate         DATE,
     l_receiptdate        DATE,
     l_shipinstruct       CHAR(25),
     l_shipmode           CHAR(10),
     l_comment            VARCHAR(44))
    INDEX IN DMS_I1
    PARTITION BY RANGE(l_shipdate)
    ( PART JAN1992 STARTING '1/1/1992'  ENDING '30/6/1992' IN DMS_D1,
      PART JULY1992 STARTING '1/7/1992' ENDING '31/12/1992' IN DMS_D2,
      PART JAN 1993 STARTING '1/1/1993' ENDING '30/6/1993' IN DMS_D3,
      PART JULY1993 STARTING '1/7/1993' ENDING '31/12/1993' IN DMS_D4);
    
    1. 创建表的 SQL 位于 EX2-8.sql 文件中,可使用下面的命令运行该文件:

      清单 34. 运行 EX2-8

      db2 –vtf EX2-8.sql

    2. 在 LINEITEM 表中创建一个索引,并将它放置在表空间 DMS_I1 中。使用如下 SQL:

      清单 35. 索引

      db2 “create index I_LINEITEM on LINEITEM(L_SHIPDATE) in DMS_I1”

    3. 使用下面的 SQL 检验和该表相关联的索引的位置:

      清单 36. 说明

      db2 “select tabname, index_tbspace from syscat.tables where tabname = ‘LINEITEM’”

      图 15. 检验索引的位置

      命令窗口

    4. 注意:索引空间是 DMS_I1。如果没有为分区表指定表空间,那么默认情况下索引位于连接着的第一个表空间。 在 CREATE TABLE 中定义表空间是很好的实践。然而,无论您是否在创建表语句 ID 中指定索引表空间,这并不限制您将来放置索引的位置。您可以在 CREATE INDEX 语句本身显式地指定索引表空间。 同一分区表的不同索引可以放置在不同的表空间。

    管理分区表

    这个实验将查看如何管理和操作分区表:

    1. 您将添加和删除分区。
    2. 您将执行对分区的转入转出操作。
    3. 在执行查看操作时将使用 DB2 命令和 SQL。

    使用现有的表添加一个新的分区

    1. 将数据导入 LINEITEM 表。创建表空间的 SQL 位于 EX3-1.sql 文件中,可使用下面的命令运行该文件:

      清单 37. 导入数据

      db2 –vtf EX3-1.sql

      图 16. 将数据导入到 LINEITEM 表

    命令窗口

    1. 注意:分区表中的记录数,以及将进行连接和分离的记录数对于说明数据库中的数据何时可用非常重要。

    2. 创建一个名为 NP_LINEITEM 的新表。脚本 EX3-2.sql 创建一个具有 87 行的新表 NP_LINEITEM:

      清单 38. 新分区

      db2 –vtf EX3-2.sql

      图 17. 将数据导入到 NP_LINEITEM 表

    命令窗口

    使用下面的命令说明为 LINEITEM 表创建的分区范围:

    清单 39. 说明
    db2 describe data partitions for table LINEITEM show detail
    
    图 18. LINEITEM 表的分区

    命令窗口

    1. 注意:LINEITEM 表目前包含 4 个数据分区。

    2. 使用Alter语句将一个新的分区连接(转入)到现有的 LINEITEM 表。

      清单 40. 新分区
    ALTER TABLE LINEITEM ATTACH PARTITION JAN1994 
    STARTING '1/1/1994' ENDING '30/6/1994'
    FROM NP_LINEITEM
    

    可以使用以下命令运行脚本 EX3-4:

    清单 41. 新分区
    db2 –vtf EX3-4.sql
    
    图 19. 脚本 EX3-4

    命令窗口

    1. 注意:LINEITEM 表被置于 SET INTEGRITY PENDING 状态。

    2. 连接后,使用

      describe data partitions
      

      命令来说明为 LINEITEM 表创建的分区范围:

      清单 42. 说明

      db2 describe data partitions for table LINEITEM show detail

      图 20. LINEITEM 表的分区

    命令窗口

    1. 注意

      :新数据分区(JAN1994)PartitionId 4 现在连接到了 LINEITEM 表。然而连接的分区的 AccessMode 值为 ‘N’ 并且 Status 的值为 ‘A’。 AccessMode 可能的值有:

      • D = 没有数据移动
      • F = 完全访问
      • N = 不访问
      • R = 只读访问

      Status 可能的值有:

      • A = 数据分区是新连接的
      • D = 数据分区是分离的
      • I = 只有在执行异步索引清除时才对条目位于目录的分离的数据分区进行维护;当所有引用分离数据分区的索引记录删除后,将删除 STATUS 值为 ‘I’ 的行。
      • Empty string = 数据分区是可见的(普通状态)
    2. 运行两个select count语句来检查连接语句涉及的两个表中数据的可用性。

      清单 43. Count Lineitem

      db2 “select count(*) from lineitem”

      图 21. select count 语句的结果

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fHcMiLd2-1576419249173)(https://www.ibm.com/developerworks/cn/education/data/dm0612read/s25a.jpg)]

    注意:LINEITEM 表最初的分区是可用的,但是 PartitionId 4 中的新数据仍不可见。

    清单 44. Count np-lineitem
    db2 “select count(*) from np_lineitem”
    
    图 22. select count 语句的结果

    命令窗口

    1. 注意:NP_LINEITEM 表现在是一个未定义的对象,在 LINEITEM 表内只能将其作为一个分区使用。

    2. 创建一个异常表并与 SET INTEGRITY 语句结合使用。执行该操作的 DDL 位于 EX3-7.sql 文件,可使用下面的命令运行该文件:

      清单 45. 异常表

      db2 –vtf EX3-7.sql

    3. 对 LINEITEM 分区表运行set integrity语句 。

      清单 46. 设置完整性
    SET INTEGRITY FOR LINEITEM <br>
    ALLOW WRITE ACCESS <br>
    IMMEDIATE CHECKED  <br>
    FOR EXCEPTION IN LINEITEM USE LINEITEM_EX
    

    执行该操作的 SQL 位于 EX3-8.sql 文件中,可以使用下面的命令运行该文件:

    清单 47. 设置完整性
    db2 –vtf EX3-8.sql
    
    图 23. EX3-8.sql 文件

    命令窗口

    1. 注意SET INTEGRITY 对于检查新连接的数据是否在范围内是必需的,它还执行对索引和其他独立对象(例如物化查询表)所有必需的维护工作。只有得到 SET INTEGRITY 语句的允许,新的数据才能变为可见。然而,当运行 SET INTEGRITY 时,可以对 LINEITEM 表中的现有数据进行完全访问,包括读和写操作。 用户应该执行 SET INTEGRITY 事务从而能够使用整个表。当运行 SET INTEGRITY 时,不能够对表执行 DDL 或其他实用类型的操作。 在这个练习中,在 NP_LINEITEM 表中创建并被连接到 LINEITEM 表的所有的行,都在连接语句指定的范围内。如果这些行中存在超出此范围的行,则需要在 SET INTEGRITY 语句中创建一个异常表来防止语句发生错误。所以推荐您始终在 SET INTEGRITY 语句中包含一个异常表。如果没有提供异常表的话,SET INTEGRITY 语句发现的错误将导致语句失败并且所有的工作都必须从头做起。如果使用大量数据时,这可能是一个长期操作。有一点值得注意,如果 SET INTEGRITY 操作失败,所有工作都需要重做,与之相比较,LOAD 仅仅抛弃存在问题的行。

    2. 对 LINEITEM 表运行select countSQL 以检查连接的分区中数据的可用性:

      清单 48. Count Lineitem

      db2 “select count(*) from lineitem”

      图 24. select count 语句的结果

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ReUztOvx-1576419249176)(https://www.ibm.com/developerworks/cn/education/data/dm0612read/s26b.jpg)]

    1. 注意:成功执行 SET INTEGRITY 操作后,LINEITEM 表应包含 PartitionId 4 的数据。

    从分区表中分离一个分区

    1. 使用describe data partitions show detail
      命令来标识一个分区的 PartitionName,您将把这个分区从 LINEITEM 分区表中分离(转出)出来。

      清单 49. 说明表

      db2 describe data partitions for table LINEITEM show detail

      图 25. LINEITEM 表的分区

    命令窗口

    1. 注意:将分离最早的分区范围 PartitionId 0。该分区的 PartitionName 是 JAN1992。将在 DETACH 操作中使用它来标识被转出的分区。同样还需注意成功执行了 SET INTEGRITY 操作后,分区 JAN1994 的 AccessMode 的值为 ‘F’,Status 值为空。TableSpId、PartObjId 和 LongTblSpId 的结果可能和这里显示的不一样。

    2. 使用

      Alter
      

      语句将 JAN1992 从 LINEITEM 表中分离(转出)。

      清单 50. Alter 表

      ALTER TABLE LINEITEM DETACH PARTITION JAN1992 INTO LINEITEM_JAN1992

      创建文件的 SQL 位于 EX3-11.sql 文件中,可使用下面的命令运行该文件:

      清单 51. Alter 表

      db2 –vtf EX3-11.sql.

      注意:将 JAN1992 成功分离后,将创建一个新的表 LINEITEM_JAN1992。在DETACH操作中没有涉及数据移动,并且位于相同表空间的新表的行为和它作为 LINEITEM 分区表的一部分时是一样的。此时不需要对 LINEITEM 表运行SET INTEGRITY语句,因为没有对 LINEITEM 表定义的 MQTs。 还有一点值得注意,如果从 Multi-Dimensional Clustering(MDC)分离一个分区从而创建了一个新表时,这个表也将是一个 MDC。这个规则同样适用于下面这个情况:从一个分布式表中分离分区从而在相同的分区组创建分布式表。执行DETACH操作后产生的表使用 MDC 索引定义而不是其他的索引。对于 MDC,在首次访问连接的表时将重新构建索引。在这种情况下,将自动对分离的分区进行索引清除操作。将从执行DETACH操作的用户 ID 继承索引的模式、权限和表空间。

    3. 运行两个select count语句检查DETACH语句涉及的两个表中的数据的可用性。

      清单 52. Count Lineitem_jan1992

      db2 “select count(*) from lineitem_jan1992”

      图 26. select count 语句的结果

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EgIPyQFS-1576419249177)(https://www.ibm.com/developerworks/cn/education/data/dm0612read/s28a.jpg)]

    注意:创建的 LINEITEM_JAN1992 表包含 38 行,它被包含在 LINEITEM 分区表的 JAN1992 分区中。

    清单 53. Count lineitem
    db2 “select count(*) from lineitem”
    
    图 27. select count 语句的结果

    命令窗口

    1. 注意:此时 LINEITEM 表完全可用,并且不包括 PART0 中的数据。

    2. 当数据被移动到分区表中,或当希望将数据加载到或直接插入分区表中时,一个更合适的方法是向现有的分区表添加一个空的分区。使用下面的命令向现有的 LINEITEM 表添加一个空的分区:

      清单 54. 说明
    db2 “ALTER TABLE LINEITEM ADD PARTITION JULY1994 
    STARTING '1/7/1994' ENDING '31/12/1994'
    图 28. 向现有的 LINEITEM 表添加一个空的分区

    命令窗口

    使用 describe data partitions show detail 命令来检验 PartitionName 为 JULY1994 的分区是否被添加到 LINEITEM 中:

    清单 55. 说明
    db2 describe data partitions for table LINEITEM show detail
    
    图 29. LINEITEM 表的分区

    命令窗口

    分区表的访问计划

    本实验将研究如何在访问计划中描述分区表:

    1. 您将更新分区表中的统计信息。

    2. 您将使用 db2expln 命令并分析结果。

    3. 您将在执行查看的操作中使用 DB2 命令和 SQL 。

    4. 对 LINEITEM 表执行RUNSTATS操作:

      清单 56. Runstats

      db2 runstats on table db2inst1.lineitem

    5. 说明以下 SQL 语句并检查说明输出:

      清单 57. 说明

      db2 “select l_shipdate,sum(l_quantity) from LINEITEM group by l_shipdate”

      要进行说明的 SQL 位于 EX4-2.sql 文件,可以使用下面的命令运行该文件:

      清单 58. 说明输出

      db2expln –d SAMPLE –t –f EX4-2.sql

      图 30. 说明输出

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0PqTV96B-1576419249179)(https://www.ibm.com/developerworks/cn/education/data/dm0612read/s30.jpg)]

    1. 注意:该 SQL 执行了 LINEITEM 表的索引扫描。说明输出中有一个关于表分区的要点需要注意,所访问的表是被分区的,并且在扫描过程中所有数据分区都将被访问。

    2. 说明下面的 SQL 语句并检查说明输出:

      清单 59. 说明
    db2 “select l_shipdate, l_partkey, l_returnflag
    from LINEITEM
    where l_shipdate between '01/01/1993' and '31/08/1993'
    and l_partkey = 49981

    要进行说明的 SQL 语句位于 EX4-3.sql 文件中,可使用下面的命令运行该文件:

    清单 60. 说明输出
    db2expln –d SAMPLE –t –f EX4-3.sql
    
    图 31. 说明输出

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8wvYnGp1-1576419249180)(https://www.ibm.com/developerworks/cn/education/data/dm0612read/s31.jpg)]

    1. 注意:这个 SQL 语句执行了 LINEITEM 表的索引扫描。在本例中,可以看到优化器能够执行数据分区排除操作。在说明输出中要注意的是关于表分区,访问的表是被分区的,将执行分区排除功能以及删除活动数据分区的值。 在本例中,活动的数据分区为 1-2。这里引用的是 syscat.datapartitions 中的序列号(seqno)而不是 describe data partitions 命令中的 PartitionId。

    2. 使用下面的 SQL 确定在前面说明示例中活动的分区的名称:

      清单 61. 说明
    db2 “select seqno,datapartitionname
    from syscat.datapartitions
    where tabname = ‘LINEITEM’ order by seqno”
    
    图 32. 分区名称

    命令窗口

    1. 注意:序列号 1 和 2 分别映射的是 JAN1993 和 JULY1993 分区名称。

    结束语

    本教程基于 IBM DB2 9 Data Partitioning 特性。您已在以下几个方面获得了第一手的经验:

    • 如何定义分区表
    • 如何将分区表放置在底层磁盘子系统
    • 如何维护分区表
    • 如何使用 DB2 Explain 说明分区表

    范围分区将数据映射到基于关键值范围的分区,用户为每一个分区建立关键值范围。例如,企业通常希望以月份为单位,将销售数据划分到各月的分区中。与 MDC 功能集合使用时,范围分区将更加方便地定位数据,从而加快通过复杂查询检索信息的速度。

    下载资源

    本文的示例脚本和数据 (exfiles.zip | 60KB)
    链接:https://pan.baidu.com/s/1cDtoMvkfUQZ_tmhHrcrocA
    提取码:7zev

    参考

    https://www.ibm.com/developerworks/cn/education/data/dm0612read/dm0612read.html?from=singlemessage&isappinstalled=0#ibm-pcon

    展开全文
  • 本文介绍了MBR与GPT这两种不同硬盘分区方案,对不同的情况下,如何正确选择MBR分区方案还是GPT分区方案,给出了建议。本文还介绍了BIOS与EFI的一些基础知识,并示例了与MBR及GPT格式分区相关的一些基础操作。你可以...
  • 一、分区存储模式下使用 MediaStore 修改图片、 二、分区存储模式下使用 MediaStore 删除图片、 三、相关文档资料、
  • ![图片说明]...我已经设置了每个文件组,分区的边界,但是这个数值是按ID行数来分区的,那么如果我想修改每个文件组分区边界,怎么修改呢????
  • SQLServer海量数据处理 - 3 表分区

    千次阅读 2013-12-23 09:44:42
    关于SQLServer表分区,这里有篇文章写的很好,我就不多废话了,转载原地址: http://www.cnblogs.com/lyhabc/p/3350121.html =====================...网上表分区的文章成千上万,但是分区之后数据的分布流向都没
  • Oracle之表分区分区索引(一)

    千次阅读 2019-05-14 08:55:59
    对于ORACLE来讲,分区是咱们开发中最常用的,什么样的场景下要进行分区,主要是针对于大数据量的,频繁查询的, 我只是说数据量大的,并没有说物理的这个大,比如你搞一个文件,数据库文件,里面来一个file文件,你搞...
  • 文章目录系列文章目录前言1.Hive的分桶1.1 分桶原理1.2 作用1.3 案例演示:创建分桶2.Hive数据导入2.1 直接向中插入数据(强烈不推荐使用)2.2 通过load加载数据(必须掌握)2.3 通过查询加载数据(必须掌握...
  • oracle数据库和分区

    千次阅读 2013-12-30 16:49:11
    分析、约束及间关系  Oracle体系结构1 Oracle体系结构2  海量数据库及分区1  海量数据库及分区2  海量数据库及分区3  海量数据库及分区4  高级SQL优化(一)  高级SQL优化(二)  高级SQL优化(三) ...
  • 数据库设计 数据库结构设计方法及原则(li)  数据库设计的三大范式:为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则...如果数据库中的所有字段都是不可分解的原子,就说明该数...
  • 使用Hive SQL插入动态分区的ParquetOOM异常分析 温馨提示:要看高清无码套图,请使用手机打开并单击图片放大查看。 Fayson的github:https://github.com/fayson/cdhproject 提示:代码块部分可以左右...
  • MySQL分区之RANGE分区

    千次阅读 2013-11-05 22:19:38
    增加分区分区表达式的,必须是递增的,不能在已有分区前面插入分区,只能递增新的分区 SELECT table_schema,table_name,partition_name, PARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_...
  • 模拟固定分区分配

    千次阅读 多人点赞 2019-11-04 15:11:48
    //***** 分区表操作 *****// Status InsertTable(SqList *L, int i, PartiType e); Status DeleteTable(SqList *L, int i, PartiType *e); int SelectPart(PCB* pPCB, SqList *pPartTable); int MallocMemory(PCB *...
  • linux磁盘分区(MBRGPT,基于centos7)实验 为什么分区 1.优化I/O性能 2.实现磁盘空间配额限制 3.提高修复速度 4.隔离系统程序 5.安装多个OS 6.采用不同文件系统 ...0号扇区:系统引导程序+主分区表+0x55...
  • JVM内存分区和GC回收算法

    千次阅读 2016-10-09 21:47:35
    1.JVM内存分区 2.GC回收算法
  • • 实施新的分区方法 • 采用数据压缩 • 使用Enterprise Manager 创建SQL 访问指导分析会话 • 使用PL/SQL 创建SQL 访问指导分析会话 • 设置SQL 访问指导分析以获取...注:REF 分区支持对子进行修剪智能化分区
  • 内容摘要:本文主要讨论分析在UEFI+GPT模式下的Windows系统(主要是最新的Win10X64)中默认的分区结构默认的分区大小,硬盘整数分区、4K对齐、起始扇区、恢复分区、ESP分区、MSR分区,哪些分区是必要的,删除...
  • 数据库分区、分表、分库、分片

    万次阅读 多人点赞 2018-06-05 09:45:13
    一、分区的概念 数据分区是一种物理数据库的设计技术,它的目的...另外,分区可以做到将的数据均衡到不同的地方,提高数据检索的效率,降低数据库的频繁IO压力分区的优点如下:1、相对于单个文件系统或是硬盘...
  • 05-Hive动态分区

    千次阅读 2016-06-02 20:57:54
    今天来玩的实验是:Hive分区表的动态分区分区是在处理大型事实表时常用的方法。分区的好处在于缩小查询扫描范围,从而提高速度。分区分为两种:静态分区static partition动态分区dynamic partition。静态分区...
  • 硬盘如何分区分区注意事项

    千次阅读 2014-07-03 00:28:14
    但是,这种操作是改变了分区表了的,磁盘会变慢。安装程度会变慢。磁盘的寿命会变短(可以说是严重影响磁盘使用寿命,所以建议不用这种方式,实在要用的话,那么就给别人弄,自己不要这么做)。原因我在 《 硬盘的...
  • 亚当斯分区曝光法俗解之三

    千次阅读 2014-12-02 22:35:14
    由于LAB模式中的L就是图片的灰度。由于这个的范围为0-100,全黑为0,全黑为100,这个除以10,基本上与分区曝光法的0-10的对应。用工具栏中的颜色吸管选中照片中人像面部高光区,得到的为77,大致为分区...
  • 如何优化MySQL千万级大,我写了6000字的解读

    万次阅读 多人点赞 2019-10-21 20:03:03
    千万级大如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充梳理,想大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是...
  • Linux下硬盘分区

    千次阅读 2018-04-15 21:29:56
    1 fdisk -l查看硬盘及分区信息我的系统(Archlinux)下的命令效果如下:由上面的图片可以得知该系统只挂载了1个硬盘,命名为sda,其有2个主分区,sda1sda2,至于为什么这么命名,可以参考我的另一篇博客: Linux...
  • DiskGenius 编辑 锁定 讨论999 DiskGenius是一款硬盘分区及数据恢复软件。它是在最初的DOS版的基础上开发而成的。...如:已删除文件恢复、分区复制、分区备份、硬盘复制等功能。另外还增加了...
  • Linux(一): 磁盘分区

    2019-12-11 21:27:52
    如何进行磁盘分区的,一个磁盘可以分多少区,主分区和逻辑分区分区挂载目录是怎么样的。BIOSUEFI分别是啥?MSDOS与GPT又是啥? 1. 磁盘连接方式与设备文件名的关系 现在主流使用的磁盘有两种,SATA接口与SAS接口...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 24,639
精华内容 9,855
关键字:

和值号码分区表图片