oracle11g表智能按月分区之前日志表700多万数据，涉及到日志表的业务查询速度很慢一个月的数据查询也将近30秒左右，研究发现oracle有表分区功能11G更加有智能按月分区功能开干：前提确认能的表可以分区w...SyntaxHighlighter.all();
oracle 11g表智能按月分区
之前日志表700多万数据，涉及到日志表的业务查询速度很慢一个月的数据查询也将近30秒左右，研究发现oracle有表分区功能 11G更加有智能按月分区功能 开干：
前提确认能的表可以分区 www.2cto.com
第一步，先创建分区的表
--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, &＃39;MONTH&＃39;))
(PARTITION P1 VALUES LESS THAN (TO_DATE(&＃39;2012-4-1&＃39;, &＃39;YYYY-MM-DD&＃39;)));
（因为我表里目前的数据最小的是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(&＃39;2008-06-01 00:00:00&＃39;,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;)),
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(&＃39;2008-06-01 00:00:00&＃39;,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;)，不知道有没有别的办法解决这个问题，不过我这样做应该也可以自己取一个自定义时间也是说得过去的
经过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(&＃39;2012-06-01 15:48:03&＃39;,&＃39;yyyy-mm-dd HH24:MI:SS&＃39;)
and sysLog.logTime<=to_date(&＃39;2012-06-30 15:48:11&＃39;,&＃39;yyyy-mm-dd HH24:MI:SS&＃39;))
--这是没有做分区的表查询 执行时间 ：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(&＃39;2012-06-01 15:48:03&＃39;,&＃39;yyyy-mm-dd HH24:MI:SS&＃39;)
and sysLog.logTime<=to_date(&＃39;2012-06-30 15:48:11&＃39;,&＃39;yyyy-mm-dd HH24:MI:SS&＃39;))
有一个说下新建的分区表我只建了 主键 其他都没弄 因为我发现再建日期索引的话查询速度慢了n多，比老表的速度还要慢，不知道这是什么原因，反正我新建的表就弄了一个主键，view sql的结构是这样的： www.2cto.com
-- 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(&＃39; 2012-04-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-05-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-06-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-07-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-08-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-09-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-10-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-11-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39; 2012-12-01 00:00:00&＃39;, &＃39;SYYYY-MM-DD HH24:MI:SS&＃39;, &＃39;NLS_CALENDAR=GREGORIAN&＃39;))
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(&＃39;2012-06-01 00:00:00&＃39;) tablespace system;.....
分区后日志查询速度快的不是一般多啊！
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有