db2表分区

我不是女神ヾ 2022-08-23 15:51 260阅读 0赞

为了提高查询效率，需要对一张大数据的表做表分区。

我直接把分区的东西贴出来吧。不细说了。

1，建表或者重建表。

/*
    这个分区的sql实际过程是先将表导出，然后删掉重建，然后导入原来数据的过程。
    这个sql也可以分成3部分执行。
    
    注意事项;
    
    1:导出文件路径和名称要根据实际情况修改，不要出现中文！
    2：因为该表的主键生成策略是自增长，所以在打入数据后再
    修改id生成方式（看sql），这里要注意id的起始值必须大于原来
    最大值（因为是自增长，防止id重复）。要在执行sql前把这个值
    查出来，修改本sql文件，RESTART WITH （比原来最大值要大）;
    select  max(id) from TB_xxxxxx_TELRECORD_PARTTEST ;
    
    
    */
    
    
    
    
    
    
    SET SCHEMA = 'xxxxxx';
    /*
    EXPORT TO
      'D:\xxxxxx_TB_xxxxxx_TELRECORD_PARTTEST1268430815.ixf'
      OF IXF 
      SELECT * FROM "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST";
    
    DROP TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST";
    */
    CREATE TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST" (
      "ID"	INTEGER	NOT NULL,
      "CALLID"	VARCHAR(20)
      "CUSTNAME"	VARCHAR(40),
      "STARTTIME"	VARCHAR(19)
    
    
    ) 
      IN "USERSPACE2"
      LONG IN "USERSPACE2"
      PARTITION BY RANGE ( "ID" NULLS LAST ) (
          PARTITION part01 STARTING MINVALUE INCLUSIVE  ENDING 2000000 INCLUSIVE,
          PARTITION part02 STARTING  2000001 INCLUSIVE  ENDING 4000000 INCLUSIVE,
          PARTITION part03 STARTING  4000001 INCLUSIVE  ENDING 6000000 INCLUSIVE,
          PARTITION part04 STARTING  6000001 INCLUSIVE  ENDING 8000000 INCLUSIVE,
          PARTITION part05 STARTING  8000001 INCLUSIVE  ENDING 10000000 INCLUSIVE,
          PARTITION part06 STARTING  10000001 INCLUSIVE  ENDING 12000000 INCLUSIVE,
          PARTITION part07 STARTING  12000001 INCLUSIVE  ENDING 14000000 INCLUSIVE,
          PARTITION part08 STARTING  14000001 INCLUSIVE  ENDING 16000000 INCLUSIVE,
          PARTITION part09 STARTING  16000001 INCLUSIVE  ENDING MAXVALUE INCLUSIVE)
      DISTRIBUTE BY HASH ( "ID" );
    
    ALTER TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST"
      DATA CAPTURE NONE
      LOCKSIZE ROW
      APPEND OFF
      NOT VOLATILE;
    
    GRANT CONTROL ON TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST" TO USER "xxxxxx";
    
    GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
        INDEX, REFERENCES ON TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST" TO USER "xxxxxx" WITH GRANT OPTION;
    
    COMMIT;
    
    
    /*
    IMPORT FROM 
      'D:\xxxxxx_TB_xxxxxx_TELRECORD_PARTTEST1268430815.ixf'
     OF IXF 
      MODIFIED BY COMPOUND=5
      NOCHECKLENGTHS
      COMMITCOUNT 1000
      INSERT INTO "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST"
     (
      "ID",
      "CALLID",
      "STARTTIME",
      "ENDTIME"
    
      );
    
    ALTER TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST"
      ALTER "ID" SET 	GENERATED BY DEFAULT
        AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
           NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER);
    
    ALTER TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST"
      ALTER COLUMN "ID" RESTART WITH 6528646;
    
    RUNSTATS ON TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST"
      ALLOW WRITE ACCESS;
    
    COMMIT;
    */

注意，还有把字符类型的字段作为分区键的，我也是用这个的。

/*
    
    说明：
    1，字符串的分区键在定义时一定要写成字符串类型，并且字符串的范围是按照
    字符串比较而定的，和整数的范围比较是不一样的。例如1到10.那么2肯定在这个
    区间。但是如果是字符串'1'到'10'，那么'2'就不在这个范围。
    所以日期（字符串类型）的定义如下(将2010年定义成一个分区)：
    PARTITION PART01 STARTING '2010-01-01 00:00:00' INCLUSIVE  ENDING '2010-12-31 59:59:59' INCLUSIVE
    2，分区区间在第1个和最后一个一定要把范围无限扩大，这样才能保证所有值都能插入。
    因为如果一条记录分区键的值不在表定义的分区区间，那么就会报错！
    
    所以第1条，可以这样定义：
    PARTITION PART01 STARTING '1000-01-01 00:00:00' INCLUSIVE  ENDING '2010-12-31 59:59:59' INCLUSIVE
    最后1条可以这样定义：
    PARTITION PART02 STARTING '2012-01-01 00:00:00' INCLUSIVE  ENDING '9999-12-31 59:59:59' INCLUSIVE
    
    这样保证分区键的值是覆盖所有情况的。这不会影响分区效果，因为分区时根据插入数据值得实际值来决定
    存在哪个分区的。
    当然整数类型就更好解决了。
    使用MINVALUE,MAXVALUE。
    
    
    */
    
    
    SET SCHEMA = 'xxxxxx';
    
    ---DROP TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST3";
    
    CREATE TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST3" (
      "ID"	INTEGER	NOT NULL	GENERATED BY DEFAULT
        AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE 20,
           NO MINVALUE, NO MAXVALUE, NO CYCLE, NO ORDER),
      "CALLID"	VARCHAR(20),
      "STARTTIME"	VARCHAR(19),
      "ENDTIME"	VARCHAR(19)
    ) 
      IN "USERSPACE2"
      PARTITION BY RANGE ( "STARTTIME" NULLS LAST ) (
          PARTITION PART01 STARTING '1000-01-01 00:00:00' INCLUSIVE  ENDING '2010-12-31 59:59:59' INCLUSIVE,
          PARTITION PART02 STARTING '2011-01-01 00:00:00' INCLUSIVE  ENDING '2011-12-31 59:59:59' INCLUSIVE,
          PARTITION PART03 STARTING '2012-01-01 00:00:00' INCLUSIVE  ENDING '9999-12-31 59:59:59' INCLUSIVE )
      DISTRIBUTE BY HASH ( "STARTTIME" );
    
    ALTER TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST3"
      DATA CAPTURE NONE
      LOCKSIZE ROW
      APPEND OFF
      NOT VOLATILE;
    
    GRANT CONTROL ON TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST3" TO USER "xxxxxx";
    
    GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
        INDEX, REFERENCES ON TABLE "xxxxxx"."TB_xxxxxx_TELRECORD_PARTTEST3" TO USER "xxxxxx" WITH GRANT OPTION;
    
    COMMIT;

至此，表分区完成，当然要检测一下分区是否起作用。

1，检验分区确实是将数据分区了。

2，检验分区确实能提高查询效率。

一;可以插入肩挑数据，看看分区的各个区数据分布。

![Center][]

![Center 1][]

二，查看分区对查询的效率。

这里我用的是对第一个分区sql的检测结果。

测试1  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxxx\_TELRECORD\_PARTTEST a  where a.id=900000  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 40秒,656毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
测试3  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxx\_TELRECORD\_PARTTEST a  where a.STARTTIME>'20140601'  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 40秒,451毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
测试4  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxxx\_TELRECORD\_PARTTEST a    
查询出结果集rs.next()  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 250毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
  
  
测试5  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxxxx\_TELRECORD\_PARTTEST a  where 1=1  
查询出结果集rs.next()  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 243毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
  
  
  
测试10  
  
  
方法selectPrepNoParam=====select distinct d.id,d.name from TB\_xxxx\_TELRECORD\_PARTTEST a,tb\_agent\_zj\_zxpd2   
b,acl\_oper c,acl\_ag\_grp d where a.id=b.tid and a.dealoper=c.logincode and c.groupid=d.id  and   
a.connectid!=''  and a.connectid!=upper('NULL')  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 46秒,59毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
  
  
  
  
  
  
  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分析\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分析\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分析\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分析\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分析\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
  
  
  
  
  
  
  
  
超过2秒的sql被认为影响用户体验。这些sql要在优化后作为优化结果的对比依据。  
  
  
  
  
包括：  
  
  
测试1  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxx\_TELRECORD\_PARTTEST a  where a.id=900000  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 40秒,656毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
测试3  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxx\_TELRECORD\_PARTTEST a  where a.STARTTIME>'20140601'  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 40秒,451毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
测试6  
  
  
方法othersqlPrepNoparam=====delete from TB\_xxxx\_TELRECORD\_PARTTEST where ID=6528646  
执行sql成功。  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 41秒,508毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
测试7  
  
  
方法othersqlPrepNoparam=====update TB\_xxxx\_TELRECORD\_PARTTEST set exammarkflag ='1'  where ID=6528645  
执行sql成功。  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 40秒,840毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
  
测试10  
  
  
方法selectPrepNoParam=====select distinct d.id,d.name from TB\_xxxxxxx\_TELRECORD\_PARTTEST a,tb\_agent\_zj\_zxpd2   
b,acl\_oper c,acl\_ag\_grp d where a.id=b.tid and a.dealoper=c.logincode and c.groupid=d.id  and   
a.connectid!=''  and a.connectid!=upper('NULL')  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 46秒,59毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
  
  
  
  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*分区以后测试\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//  
  
  
  
  
测试1  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxx\_TELRECORD\_PARTTEST a  where a.id=900000  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 4秒,847毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
测试3  
  
  
方法selectPrepNoParam=====select \* from TB\_xxxxxxx\_TELRECORD\_PARTTEST a  where a.STARTTIME>'20140601'  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 38秒,228毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
测试6  
  
  
  
  
方法othersqlPrepNoparam=====delete from TB\_xxxxx\_TELRECORD\_PARTTEST where ID=6528645  
执行sql成功。  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 354毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
测试7  
  
  
方法othersqlPrepNoparam=====update TB\_xxxx\_TELRECORD\_PARTTEST set exammarkflag ='1'  where ID=6528644  
执行sql成功。  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 338毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
测试10  
  
  
方法selectPrepNoParam=====select distinct d.id,d.name from TB\_xxxxxxx\_TELRECORD\_PARTTEST a,tb\_agent\_zj\_zxpd2   
b,acl\_oper c,acl\_ag\_grp d where a.id=b.tid and a.dealoper=c.logincode and c.groupid=d.id  and   
a.connectid!=''  and a.connectid!=upper('NULL')  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
执行时间： 339毫秒  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
  
  
  
  
//\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*end\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*//

[Center]: /images/20220823/ed4c08070cc34d86acc3c38eda2d9716.png
[Center 1]: /images/20220823/b89e13c7ca3d4cd396a8ba95203e2c3d.png