对分区表的某个区进行分割

最新推荐文章于 2023-03-24 15:49:15 发布

cocohill1991

最新推荐文章于 2023-03-24 15:49:15 发布

阅读量195

点赞数

-----------
背景
-----------
2008-12-31 19:52

某银行希望将一业务数据库进行调整，原表 stdtrandata 是一个大分区表，按照时间进行的范围分区，每个分区存放一个月份的数据，但是只有12个分区，客户想再增加一年的分区数，分区表原来有一个主键，主键对应的分区索引为局部索引，还有3个局部索引，还有一个索引。
*.) OS: aix 53
*.) db:9201 64bit
*.) Mem:1.8G
*.) CPU:2

-----------
思路一：
-----------
*.) 使用脚本，创建一个新表 a，新表符合用户要求
*.) 使用 insert into a/*+append*/ a select * from stdtrandata;
*.) 删除表 stdtrandata 的主键和索引
*.) 将 stdtrandata 重命名为 b
*.) 将 a 重命名为 stdtrandata
*.) 然后使用脚本，为表 stdtrandata 创建对应分区数目局部索引和主键。

-----------
思路二：
-----------
*.) 直接将分区表进行分割
*.) 删除主键和索引
*.) 重建主键和索引

本文采用第二种思路。

-----------
操作说明
-----------
今天是个很忙的日期，系统在下午完成了批处理后，相对较闲。

1.) 首先停掉监听器
2.) 备份表 stdtrandata ，将数据和索引全部备份，大概有12G数据，备份1个小时左右。

3.) 使用split创建新分区
  按照下面格式，编辑好分割分区的脚本：
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20090101') INTO ( PARTITION DP200812 tablespace TS_FM200812,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20090201') INTO ( PARTITION DP200901 tablespace TS_FM200901,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20090301') INTO ( PARTITION DP200902 tablespace TS_FM200902,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20090401') INTO ( PARTITION DP200903 tablespace TS_FM200903,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20090501') INTO ( PARTITION DP200904 tablespace TS_FM200904,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20090601') INTO ( PARTITION DP200905 tablespace TS_FM200905,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20090701') INTO ( PARTITION DP200906 tablespace TS_FM200906,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20090801') INTO ( PARTITION DP200907 tablespace TS_FM200907,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20090901') INTO ( PARTITION DP200908 tablespace TS_FM200908,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20091001') INTO ( PARTITION DP200909 tablespace TS_FM200909,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20091101') INTO ( PARTITION DP200910 tablespace TS_FM200910,PARTITION DP000001 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP000001 at ('20091201') INTO ( PARTITION DP200911 tablespace TS_FM200911,PARTITION DP999999 );
  ALTER TABLE stdtrandata SPLIT PARTITION DP999999 at ('20100101') INTO ( PARTITION DP200912 tablespace TS_FM200912,PARTITION DP000001 tablespace TS_FM999999 );

上述过程，除第一语句较慢外，其他都很快完成，因为刚开始，DP999999中包含较多数据。

4.) 删除主键和索引

  drop index BT_STDTRANDATA_P01;
  drop index BT_STDTRANDATA_P02;
  drop index BT_STDTRANDATA_P03;
  drop index bt_stdtrandata_g01;

  alter table stdtrandata drop primary key;

5.) 重建主键和索引

  首先按照下述格式进行编辑：

  ALTER TABLE STDTRANDATA
         ADD ( CONSTRAINT PK_STDTRANDATA PRIMARY KEY (ID,TRAN_DATE)
         USING INDEX LOCAL
  (
    PARTITION NP000000 TABLESPACE   ts_ndx_fm000000,
    PARTITION NP200801 TABLESPACE   ts_ndx_fm200801,
    PARTITION NP200802 TABLESPACE   ts_ndx_fm200802,
    PARTITION NP200803 TABLESPACE   ts_ndx_fm200803,
    PARTITION NP200804 TABLESPACE   ts_ndx_fm200804,
    PARTITION NP200805 TABLESPACE   ts_ndx_fm200805,
    PARTITION NP200806 TABLESPACE   ts_ndx_fm200806,
    PARTITION NP200807 TABLESPACE   ts_ndx_fm200807,
    PARTITION NP200808 TABLESPACE   ts_ndx_fm200808,
    PARTITION NP200809 TABLESPACE   ts_ndx_fm200809,
    PARTITION NP200810 TABLESPACE   ts_ndx_fm200810,
    PARTITION NP200811 TABLESPACE   ts_ndx_fm200811,
    PARTITION NP200812 TABLESPACE   ts_ndx_fm200812,
    PARTITION NP200901 TABLESPACE   ts_ndx_fm200901,
    PARTITION NP200902 TABLESPACE   ts_ndx_fm200902,
    PARTITION NP200903 TABLESPACE   ts_ndx_fm200903,
    PARTITION NP200904 TABLESPACE   ts_ndx_fm200904,
    PARTITION NP200905 TABLESPACE   ts_ndx_fm200905,
    PARTITION NP200906 TABLESPACE   ts_ndx_fm200906,
    PARTITION NP200907 TABLESPACE   ts_ndx_fm200907,
    PARTITION NP200908 TABLESPACE   ts_ndx_fm200908,
    PARTITION NP200909 TABLESPACE   ts_ndx_fm200909,
    PARTITION NP200910 TABLESPACE   ts_ndx_fm200910,
    PARTITION NP200911 TABLESPACE   ts_ndx_fm200911,
    PARTITION NP200912 TABLESPACE   ts_ndx_fm200912,
    PARTITION NP000001 TABLESPACE   TS_NDX_FM999999
   ) ) ;


  CREATE INDEX BT_STDTRANDATA_P01 ON STDTRANDATA
  (tran_date,appsys)
  LOCAL (
    PARTITION NP000000 TABLESPACE   ts_ndx_fm000000,
    PARTITION NP200801 TABLESPACE   ts_ndx_fm200801,
    PARTITION NP200802 TABLESPACE   ts_ndx_fm200802,
    PARTITION NP200803 TABLESPACE   ts_ndx_fm200803,
    PARTITION NP200804 TABLESPACE   ts_ndx_fm200804,
    PARTITION NP200805 TABLESPACE   ts_ndx_fm200805,
    PARTITION NP200806 TABLESPACE   ts_ndx_fm200806,
    PARTITION NP200807 TABLESPACE   ts_ndx_fm200807,
    PARTITION NP200808 TABLESPACE   ts_ndx_fm200808,
    PARTITION NP200809 TABLESPACE   ts_ndx_fm200809,
    PARTITION NP200810 TABLESPACE   ts_ndx_fm200810,
    PARTITION NP200811 TABLESPACE   ts_ndx_fm200811,
    PARTITION NP200812 TABLESPACE   ts_ndx_fm200812,
    PARTITION NP200901 TABLESPACE   ts_ndx_fm200901,
    PARTITION NP200902 TABLESPACE   ts_ndx_fm200902,
    PARTITION NP200903 TABLESPACE   ts_ndx_fm200903,
    PARTITION NP200904 TABLESPACE   ts_ndx_fm200904,
    PARTITION NP200905 TABLESPACE   ts_ndx_fm200905,
    PARTITION NP200906 TABLESPACE   ts_ndx_fm200906,
    PARTITION NP200907 TABLESPACE   ts_ndx_fm200907,
    PARTITION NP200908 TABLESPACE   ts_ndx_fm200908,
    PARTITION NP200909 TABLESPACE   ts_ndx_fm200909,
    PARTITION NP200910 TABLESPACE   ts_ndx_fm200910,
    PARTITION NP200911 TABLESPACE   ts_ndx_fm200911,
    PARTITION NP200912 TABLESPACE   ts_ndx_fm200912,
    PARTITION NP000001 TABLESPACE   TS_NDX_FM999999
  );

  CREATE INDEX BT_STDTRANDATA_P02 ON STDTRANDATA
  (tran_date,CUSTTYPE)
  LOCAL (
    PARTITION NP000000 TABLESPACE   ts_ndx_fm000000,
    PARTITION NP200801 TABLESPACE   ts_ndx_fm200801,
    PARTITION NP200802 TABLESPACE   ts_ndx_fm200802,
    PARTITION NP200803 TABLESPACE   ts_ndx_fm200803,
    PARTITION NP200804 TABLESPACE   ts_ndx_fm200804,
    PARTITION NP200805 TABLESPACE   ts_ndx_fm200805,
    PARTITION NP200806 TABLESPACE   ts_ndx_fm200806,
    PARTITION NP200807 TABLESPACE   ts_ndx_fm200807,
    PARTITION NP200808 TABLESPACE   ts_ndx_fm200808,
    PARTITION NP200809 TABLESPACE   ts_ndx_fm200809,
    PARTITION NP200810 TABLESPACE   ts_ndx_fm200810,
    PARTITION NP200811 TABLESPACE   ts_ndx_fm200811,
    PARTITION NP200812 TABLESPACE   ts_ndx_fm200812,
    PARTITION NP200901 TABLESPACE   ts_ndx_fm200901,
    PARTITION NP200902 TABLESPACE   ts_ndx_fm200902,
    PARTITION NP200903 TABLESPACE   ts_ndx_fm200903,
    PARTITION NP200904 TABLESPACE   ts_ndx_fm200904,
    PARTITION NP200905 TABLESPACE   ts_ndx_fm200905,
    PARTITION NP200906 TABLESPACE   ts_ndx_fm200906,
    PARTITION NP200907 TABLESPACE   ts_ndx_fm200907,
    PARTITION NP200908 TABLESPACE   ts_ndx_fm200908,
    PARTITION NP200909 TABLESPACE   ts_ndx_fm200909,
    PARTITION NP200910 TABLESPACE   ts_ndx_fm200910,
    PARTITION NP200911 TABLESPACE   ts_ndx_fm200911,
    PARTITION NP200912 TABLESPACE   ts_ndx_fm200912,
    PARTITION NP000001 TABLESPACE   TS_NDX_FM999999
  );


  CREATE INDEX BT_STDTRANDATA_P03 ON STDTRANDATA
  (idtype,cust_code)
  LOCAL (
    PARTITION NP000000 TABLESPACE   ts_ndx_fm000000,
    PARTITION NP200801 TABLESPACE   ts_ndx_fm200801,
    PARTITION NP200802 TABLESPACE   ts_ndx_fm200802,
    PARTITION NP200803 TABLESPACE   ts_ndx_fm200803,
    PARTITION NP200804 TABLESPACE   ts_ndx_fm200804,
    PARTITION NP200805 TABLESPACE   ts_ndx_fm200805,
    PARTITION NP200806 TABLESPACE   ts_ndx_fm200806,
    PARTITION NP200807 TABLESPACE   ts_ndx_fm200807,
    PARTITION NP200808 TABLESPACE   ts_ndx_fm200808,
    PARTITION NP200809 TABLESPACE   ts_ndx_fm200809,
    PARTITION NP200810 TABLESPACE   ts_ndx_fm200810,
    PARTITION NP200811 TABLESPACE   ts_ndx_fm200811,
    PARTITION NP200812 TABLESPACE   ts_ndx_fm200812,
    PARTITION NP200901 TABLESPACE   ts_ndx_fm200901,
    PARTITION NP200902 TABLESPACE   ts_ndx_fm200902,
    PARTITION NP200903 TABLESPACE   ts_ndx_fm200903,
    PARTITION NP200904 TABLESPACE   ts_ndx_fm200904,
    PARTITION NP200905 TABLESPACE   ts_ndx_fm200905,
    PARTITION NP200906 TABLESPACE   ts_ndx_fm200906,
    PARTITION NP200907 TABLESPACE   ts_ndx_fm200907,
    PARTITION NP200908 TABLESPACE   ts_ndx_fm200908,
    PARTITION NP200909 TABLESPACE   ts_ndx_fm200909,
    PARTITION NP200910 TABLESPACE   ts_ndx_fm200910,
    PARTITION NP200911 TABLESPACE   ts_ndx_fm200911,
    PARTITION NP200912 TABLESPACE   ts_ndx_fm200912,
    PARTITION NP000001 TABLESPACE   TS_NDX_FM999999
  );




  create index bt_stdtrandata_g01 on stdtrandata (file_name,curr) tablespace INDEX_TS;

6.) 创建过程简要说明：

  topas进行观测如下：

  Topas Monitor for host:    localhost            EVENTS/QUEUES    FILE/TTY
  Wed Dec 31 20:19:37 2008   Interval: 2         Cswitch    1754 Readch    11.7M
                                                  Syscall     867 Writech   12.2M
  Kernel    4.5   |##                          | Reads       320 Rawin         0
  User     53.1   |################            | Writes      164 Ttyout      374
  Wait     23.8   |#######                     | Forks         0 Igets         0
  Idle     18.5   |######                      | Execs         0 Namei         7
                                                  Runqueue    0.5 Dirblk        0
  Network KBPS   I-Pack O-Pack   KB-In KB-Out Waitqueue   0.0
  en0       0.4      0.5     0.5     0.0     0.4
  lo0       0.0      0.0     0.0     0.0     0.0 PAGING           MEMORY
                                                  Faults       15 Real,MB    1824
  Disk    Busy%     KBPS     TPS KB-Read KB-Writ Steals        0 % Comp     68.4
  hdisk0    0.0   5648.0    82.0     0.0 5648.0 PgspIn        0 % Noncomp 32.3
  hdisk3    0.0    956.0    19.5     0.0   956.0 PgspOut       0 % Client    3.9
  hdisk2    0.0 12046.0   180.0 6780.0 5266.0 PageIn        0
  cd0       0.0      0.0     0.0     0.0     0.0 PageOut    1659 PAGING SPACE
  hdisk1    0.0      0.0     0.0     0.0     0.0 Sios        109 Size,MB    2048
                                                                   % Used     41.2
  Name            PID CPU% PgSp Owner           NFS (calls/sec) % Free     58.7
  oracle       299182 57.8   7.4 amls            ServerV2       0
  aioserve     340134   0.8   0.1 root            ClientV2       0   Press:
  aioserve     364722   0.8   0.1 root            ServerV3       0   "h" for help
  oracle       319644   0.7   5.3 oracle          ClientV3       0   "q" to quit
  aioserve     418032   0.5   0.1 root
  aioserve     122952   0.3   0.1 root
  topas        536600   0.2   1.4 oracle
  aioserve     352428   0.0   0.1 root
  aioserve     389310   0.0   0.1 root
  aioserve     381116   0.0   0.1 root
  i4llmd       266418   0.0   1.0 root
  oracle       323742   0.0 10.4 oracle
  oracle       315546   0.0   7.2 oracle
  aioserve     368820   0.0   0.1 root
  aioserve     274624   0.0   0.1 root
  aioserve     372918   0.0   0.1 root
  gil           57372   0.0   0.1 root
  oracle       311450   0.0   8.5 oracle
  dtscreen     401468   0.0   0.5 root

7.) 观察索引表空间的使用情况
  创建索引期间,从对应的表空间的使用情况，我们可以知道哪个索引分区正在创建索引


  TABLESPACE_NAME        TOT_SIZE      TOT_FREE PCT_FREE     MAX_FREE CHUNKS_FREE
  ----------------- ------------- ------------- -------- ------------ -----------
  TS_NDX_FM000000     524,288,000   439,287,808       84 439,287,808           1
  TS_NDX_FM200801     639,631,360   320,798,720       50 320,798,720           1
  TS_NDX_FM200802     524,288,000   306,118,656       58 306,118,656           1
  TS_NDX_FM200803   1,048,576,000   704,512,000       67 356,450,304           2
  TS_NDX_FM200804     597,688,320   295,632,896       49 295,632,896           1
  TS_NDX_FM200805     597,688,320   295,632,896       49 295,632,896           1
  TS_NDX_FM200806     859,832,320   398,393,344       46 398,393,344           1
  TS_NDX_FM200807     671,088,640   360,644,608       54 360,644,608           1
  TS_NDX_FM200808     671,088,640   369,033,216       55 369,033,216           1
  TS_NDX_FM200809     975,175,680   555,679,744       57 555,679,744           1
  TS_NDX_FM200810     692,060,160   373,227,520       54 373,227,520           1
  TS_NDX_FM200811     650,117,120   348,061,696       54 348,061,696           1
  TS_NDX_FM200812     524,288,000   104,792,064       20 104,792,064           1
  TS_NDX_FM200901     524,288,000   524,025,856      100 524,025,856           1

8.) 创建局部索引的过程还是比较快，大概20分钟能创建完一个，而普通的索引的创建，大概用了40-50分钟。
创建完后，用户测试一下，正常，性能得到提升。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195785/viewspace-524832/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195785/viewspace-524832/

cocohill1991

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
对分区表的某个区进行分割

-----------背景-----------2008-12-31 19:52某银行希望将一业务数据库进行调整，原表 stdtrandata 是一个大分区表，按照时间进行的范围分区，每个分区存放一个月份的数据，但是只有12...
复制链接

扫一扫