oracle 9i 大数据表按月分区

oracle 9i的分区 比11G少了一个自动分区的功能 所以分区表只能自己先手动指定

如下表示日志表 按照字段logtime range分区 每个月创建一个分区

 -- Range分区：

--　　Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

   --    如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。

   --    在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

 
create table sys_log_fq (  pid            NUMBER not null,
  yhdm           VARCHAR2(60),
  bmdm           VARCHAR2(24),
  pdaid          VARCHAR2(1024),
  simid          VARCHAR2(100),
  logmodule      VARCHAR2(2),
  logtype        VARCHAR2(8),
  operatetype    CHAR(2),
  methodname     VARCHAR2(200),
  methodinfo     VARCHAR2(4000),
  execstatus     VARCHAR2(2),
  execerrorinfo  VARCHAR2(2000),
  alltimespent   NUMBER,
  proxytimespent NUMBER,
  logcontent     VARCHAR2(4000),
  logtime        DATE,
  memo           VARCHAR2(400),
  returnflag     CHAR(2),
  returninfo     VARCHAR2(2000),
  gpsx           VARCHAR2(40),
  gpsy           VARCHAR2(40))tablespace test_DATA
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  ) partition by range (logtime)

(
partition p1 values less than (to_date('2012-04-1', 'yyyy-mm-dd')),   --时间范围小于'2012-04-1'的数据放

    -- 一个分区
partition p2 values less than (to_date('2012-05-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2012-06-1', 'yyyy-mm-dd')),
partition p4 values less than (to_date('2012-07-1', 'yyyy-mm-dd')),
partition p5 values less than (to_date('2012-08-1', 'yyyy-mm-dd')),
partition p6 values less than (to_date('2012-09-1', 'yyyy-mm-dd')),
partition p7 values less than (to_date('2012-10-1', 'yyyy-mm-dd')),
partition p8 values less than (to_date('2012-11-1', 'yyyy-mm-dd')),
partition p9 values less than (to_date('2012-12-1', 'yyyy-mm-dd')),

partition p10 values less than (maxvalue)           --如果某些记录暂无法预测范围，可以创建maxvalue分区，所----有不在指定范围内的记录都会被存储到maxvalue所在分区中。
) 

 

 

如果是已经存在的普通表则可以新建一个分区表进行中转

 

把原先普通表的数据导入到分区表 然后renme下表名

 

 导表通过  insert into sys_log_fq  (select * from sys_log)，

数据太大 9I下数据库容导入易报错 建议分时间范围 数据分批导入。

 

分区后 实验测试得出比不分区的表一个月内查询速度快20秒（具体根据您的表数据情况）

 

如果要对分区后的表字段logtime值就行修改如果修改后的值不是该分区的范围 则会报错

为了解决这个问题，可以使用如下命令更改分区表的属性：

SQL> alter table 表名 enable row movement;

如果新插入的数据logtime是2013年的 对比上面的分区不在p1-p9分区范围 那么它会默认放进p10分区

这时候2013这个logtime值的数据只有在p10分区内才能查询到

时间一长p10 分区内的数据将会越来越多，当然需要维护，就需要新增分区

--在有maxvalue的情况下添加分区要用split语法

   alter table sys_log split partition p10 at (to_date('2013-12-1', 'yyyy-mm-dd'))
    into (partition p11, partition p10);

执行完这句  p10分区内已经查询不到2013年的数据了，只能在p11分区内查询到(实验得出，绝非理论推断)

如果不想每次都手动写新增分区语句去数据库执行 那么久可以写一个定时job

可以每个月 月么生成一个下个月的分区,(哎,用过oracle 11G的分区后就觉得9I 的分区好麻烦- -!)

 

下面是常用的分区表sql

--查询当前用户下分区表的分区信息：
SELECT * FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME='SYS_LOG';
--查询某分区下的数据量：
SELECT COUNT(*) FROM sys_log PARTITION(P2);

--查询索引、表上在那些列上创建了分区：
SELECT * FROM USER_PART_KEY_COLUMNS;

--添加分区 – 在最后一个分区之后添加新分区
ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000); 
--拆分分区 - 将一个大分区中的记录拆分到两个分区中
[sql]
ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);

  --查询每个分区表的分区的大小以及分区表的索引等
   SELECT *  FROM dba_segments t WHERE t.segment_name ='表名' and owner='用户名';