oracle 11g表智能按月分区

分区是一把双刃剑 要分区绝对要根据实际情况来分析 而我所遇到的情况及时最利于分区的

sql编程的艺术 作者说过 “对分区表进行查询，当数据按分区键均匀分布时，受益最大。”

 

而我采用的分区键是日子表的日志插入时间 按照一个月分一区，因为这个表每个月都有基本平衡的频率的数据插入， 这样表中的所有数据就可以均匀的分布到每一区。

 

令人激动的一天不算之前的学习研究 这个就花了我一下午

之前日志表700多万数据，涉及到日志表的业务查询速度很慢一个月的数据查询也将近30秒左右，研究发现oracle有表分区功能 11G更加有智能按月分区功能 开干：

前提确认能的表可以分区

第一步，先创建分区的表

--INTERVAL分区
--这个其实是范围分区的增强功能，通过这个功能可以实现在需要的时候自动的实现新的分区的添加，从而省去了你不断的ADD或者SPLIT新的分区。

CREATE TABLE SYS_LOG_TEM(pid            NUMBER not null,
  yhdm           VARCHAR2(30),
  bmdm           VARCHAR2(12),
  pdaid          VARCHAR2(512),
  simid          VARCHAR2(50),
  logmodule      VARCHAR2(1),
  logtype        VARCHAR2(4),
  operatetype    CHAR(1),
  methodname     VARCHAR2(100),
  methodinfo     VARCHAR2(2000),
  execstatus     VARCHAR2(1),
  execerrorinfo  VARCHAR2(1000),
  alltimespent   NUMBER,
  proxytimespent NUMBER,
  logcontent     VARCHAR2(4000),
  logtime        DATE default sysdate,
  memo           VARCHAR2(200),
  returnflag     CHAR(1),
  returninfo     VARCHAR2(1000),
  gpsx           VARCHAR2(20),
  gpsy           VARCHAR2(20))
  PARTITION BY RANGE (logtime)
  INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  (PARTITION P1 VALUES LESS THAN (TO_DATE('2012-4-1', 'YYYY-MM-DD')));

 

（因为我表里目前的数据最小的是4月份的，所以我让它从四月份开始分区）

 

第二步，把老的日志表的数据插入到新建的分区表  insert into SYS_LOG_TEM   select * from SYS_LOG;

  insert into SYS_LOG_TEM
  select pid,
         yhdm,
         bmdm,
         pdaid,
         simid,
         logmodule,
         logtype,
         operatetype,
         methodname,
         methodinfo,
         execstatus,
         execerrorinfo,
         alltimespent,
         proxytimespent,
         logcontent,
         nvl(logtime,to_date('2008-06-01 00:00:00','yyyy-MM-dd HH24:mi:ss')),
         memo,
         returnflag,
         returninfo,
         gpsx,
         gpsy
    from SYS_LOG;

 

 

数据7272844条 总共花了我7分钟多

 

这里之所以对logtim字段做null处理是因为一旦oracle 表做的按月分区就不能让时间字段为null否则 更新的时候会报错 ora-14402 更新分区关键字列将导致分区的更改
update sys_log_tem set logtime=null where logtime=to_date('2008-06-01 00:00:00','yyyy-MM-dd HH24:mi:ss')，不知道有没有别的办法解决这个问题，不过我这样做应该也可以自己取一个自定义时间也是说得过去的

 

 

经过plsql上的测试

查询速度对比

--做了按每月分区的表查询 执行时间 ：0.468秒
select count(pid) from ( select  sysLog.pid,sysLog.yhdm,sysLog.operateType,sysLog.methodName,
sysLog.pdaId,sysLog.simId,sysLog.logContent,sysLog.logModule,sysLog.logTime from sys_log_tem sysLog,
Acl_User u where sysLog.yhdm(+)=u.yhdm  and sysLog.logTime>=to_date('2012-06-01 15:48:03','yyyy-mm-dd HH24:MI:SS')
 and sysLog.logTime<=to_date('2012-06-30 15:48:11','yyyy-mm-dd HH24:MI:SS'))

 --这是没有做分区的表查询   执行时间 ：18秒
 select count(pid) from ( select  sysLog.pid,sysLog.yhdm,sysLog.operateType,sysLog.methodName,
sysLog.pdaId,sysLog.simId,sysLog.logContent,sysLog.logModule,sysLog.logTime from Sys_Log sysLog,
Acl_User u where sysLog.yhdm(+)=u.yhdm  and sysLog.logTime>=to_date('2012-06-01 15:48:03','yyyy-mm-dd HH24:MI:SS')
 and sysLog.logTime<=to_date('2012-06-30 15:48:11','yyyy-mm-dd HH24:MI:SS'))

 

有一个说下新建的分区表我只建了 主键 其他都没弄 因为我发现再建日期索引的话查询速度慢了n多，比老表的速度还要慢，不知道这是什么原因，反正我新建的表就弄了一个主键，view sql的结构是这样的：

-- Create table
create table SYS_LOG
(
  pid            NUMBER not null,
  yhdm           VARCHAR2(30),
  bmdm           VARCHAR2(12),
  pdaid          VARCHAR2(512),
  simid          VARCHAR2(50),
  logmodule      VARCHAR2(1),
  logtype        VARCHAR2(4),
  operatetype    CHAR(1),
  methodname     VARCHAR2(100),
  methodinfo     VARCHAR2(2000),
  execstatus     VARCHAR2(1),
  execerrorinfo  VARCHAR2(1000),
  alltimespent   NUMBER,
  proxytimespent NUMBER,
  logcontent     VARCHAR2(4000),
  logtime        DATE default sysdate,
  memo           VARCHAR2(200),
  returnflag     CHAR(1),
  returninfo     VARCHAR2(1000),
  gpsx           VARCHAR2(20),
  gpsy           VARCHAR2(20)
)
partition by range (LOGTIME)
(
  partition P1 values less than (TO_DATE(' 2012-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P22 values less than (TO_DATE(' 2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P23 values less than (TO_DATE(' 2012-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P24 values less than (TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P25 values less than (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P26 values less than (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P27 values less than (TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P28 values less than (TO_DATE(' 2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SYS_P29 values less than (TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SYSTEM
    pctfree 10
    pctused 40
    initrans 1
    maxtrans 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);
-- Create/Recreate primary, unique and foreign key constraints
alter table SYS_LOG
  add constraint PID primary key (PID)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

 

11g分区表按时间自动创建(Interval Partitioning) 我这个用到了oracle 11G的新特性

INTERVAL分区
这个其实是范围分区的增强功能，通过这个功能可以实现在需要的时候自动的实现新的分区的添加，从而省去了你不断的ADD或者SPLIT新的分区。

如果是9i的话 必须把分区的日期写死 根据你的表里的日志值，因为日志表在实时增加数据，你还得定期去手动新建 新的分区,比如这样alter table sys_log_tem add PARTITION logtime values less than('2012-06-01 00:00:00') tablespace system;.....

 

分区后日志查询速度快的不是一般多啊  ！！