关于ORACLE表分区实践

上一篇讲了很多关于分区的技术，但是怎么做分区呢，怎么管理分区呢，还需要细细道来。

对于交易流水来说，不管数据量有多大，它一定是按天进来的，这个看实际的分区粒度，分区不是越大越好，也不是越小越高，主要看每个分区的实际数据量的大小，如果一天的数据都有上百万，而查询要求只要看当天的，而且查询比较频繁，那按天做分区，创建7个分区，超过一定天数的，就可以把这个分区的数据移走，以减轻系统负担，对于我现在的情况来说，我一个月的数据量，还是可以处理的，我就建12个分区，以月份为分区键。

 

还有一个问题就是，如果我在系统建设之初，如果想到这些就好了直接在建表脚本里创建分区就好了，数据库可以自己管理数据，它知道应该把新加进来的数据往那里放，可是，最初没有规划，现在数据量已经非常大了，那我要怎么办呢？

如果有一表先是没有分区，像这个样子：

CREATE TABLE PROBLEM_TICKETS
(
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,

    tran_date  date

)

 

我现在希望把它建成一个分区表，像下面一样

CREATE TABLE PROBLEM_TICKETS
(
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,

    tran_date  date ,

    par             VARCHAR2(2)     --分区段，记交易月份
)
PARTITION BY LIST (par)
(
      PARTITION P_01   VALUES ('01')  TABLESPACE          PROB_TS01,

      PARTITION P_02   VALUES ('02')  TABLESPACE          PROB_TS02, 

      PARTITION P_03   VALUES ('03')  TABLESPACE          PROB_TS03, 

      PARTITION P_04   VALUES ('04')  TABLESPACE          PROB_TS04, 

      PARTITION P_05   VALUES ('05')  TABLESPACE          PROB_TS05, 

      PARTITION P_06   VALUES ('06')  TABLESPACE          PROB_TS06, 

      PARTITION P_07   VALUES ('07')  TABLESPACE          PROB_TS07, 

      PARTITION P_08   VALUES ('08')  TABLESPACE          PROB_TS08, 

      PARTITION P_09   VALUES ('09')  TABLESPACE          PROB_TS09, 

      PARTITION P_10   VALUES ('10')  TABLESPACE          PROB_TS10, 

      PARTITION P_11   VALUES ('11')  TABLESPACE          PROB_TS11, 

      PARTITION P_12   VALUES ('12')  TABLESPACE          PROB_TS12

);

大家一定注意到了，为什么我的新表加了一个字段par，这是我用于建分区的键，没有办法，我的表是按交易日期来做的，而分区的KEY又不可以通过函数来做，那只好加一个字段，用于让ORACLE知道这些数据应该放到那里去。

然后执行下下update PROBLEM_TICKETS set par=to_char(tran_date,'MM');

这样数据有有KEY了，可以往下做了。

 

然后建一张新表，一张带分区的表，这张表做数据转换，即先把旧表的数据放过来，放过来的时候，数据就被分区了，然后再从这个表，把数据拿回去，就可以了。

CREATE TABLE PROBLEM_TICKETS_NEW
(
    PROBLEM_ID   NUMBER(7) NOT NULL PRIMARY KEY,

    tran_date  date ,

    par             VARCHAR2(2)     --分区段，记交易月份
)
PARTITION BY LIST (par)
(
      PARTITION P_01   VALUES ('01')  TABLESPACE          PROB_TS01,

      PARTITION P_02   VALUES ('02')  TABLESPACE          PROB_TS02, 

      PARTITION P_03   VALUES ('03')  TABLESPACE          PROB_TS03, 

      PARTITION P_04   VALUES ('04')  TABLESPACE          PROB_TS04, 

      PARTITION P_05   VALUES ('05')  TABLESPACE          PROB_TS05, 

      PARTITION P_06   VALUES ('06')  TABLESPACE          PROB_TS06, 

      PARTITION P_07   VALUES ('07')  TABLESPACE          PROB_TS07, 

      PARTITION P_08   VALUES ('08')  TABLESPACE          PROB_TS08, 

      PARTITION P_09   VALUES ('09')  TABLESPACE          PROB_TS09, 

      PARTITION P_10   VALUES ('10')  TABLESPACE          PROB_TS10, 

      PARTITION P_11   VALUES ('11')  TABLESPACE          PROB_TS11, 

      PARTITION P_12   VALUES ('12')  TABLESPACE          PROB_TS12

);

修改一下下面的过程块，把引号里的'SCHEMA'改成实际的用户名，'OLD_TABLE'表名改成实际的源表名，'NEW_TABLE'改成是中间表名，执行下面的过程。

declare
begin
--1.检查该表OLD_TABLE表不满足重定义的条件

DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA','OLD_TABLE');

--2.创建修改后的新表NEW_TABLE  这一步就是上面建临时表的过程
--在用一个方案中建立一个空的中间表，根据重定义后你期望得到的结构建立中间表。比如：采用分区表，增加了COLUMN等

--3.如果映射方法没有提供，则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法，则只考虑映射方法中给出的列。如果----没有给出重定义方法，则认为使用主键方式

DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA','OLD_TABLE','NEW_TABLE');

--4.进行一次数据同步（其实是一次物化视图的刷新)

dbms_redefinition.SYNC_INTERIM_TABLE('SCHEMA','OLD_TABLE','NEW_TABLE');

--5.执行完FINISH_REDEF_TABLE()过程后，原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled

--的约束在原始表上处于enabled状态

DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA','OLD_TABLE','NEW_TABLE');

end;

 

执行完了以后，查看一下源表，看建表脚本是不是已经带了分区了，如果有的话，那恭喜你，分区成功了。

 

至于分区的好不好呢，你可以通过下面的SQL语句，查看一下，你每个分区的多少数据量
select * FROM USER_TAB_PARTITIONS ORDER BY TABLE_NAME,PARTITION_NAME;

 

正常的情况下呢，每个分区都有数据，并且数据分布均匀的话呢，算是分区策略比较成功，如果有些分区就没有用呢，那就有问题了，具体问题具体对待。分区是需要管理的，分区并不是一劳永逸的解决了问题，因为以我现在的分区为例，如果数据跨年了，那数据就会重复使用分区，那么就要定时去备份清理分区的数据。具体操作参考上一篇《关于ORACLE分区表的概念及操作》。