创建表分区的总结
最近接手一个需求，需要展示主机的性能值(指主机的CPU利用率)主机60台,每台10分钟产生条记录。每天数据量60*6*24=8640 一年300W条记录 结合一些业务要求 展示数据时间大概要10秒左右。不符合顾客需要，所以就需要进行优化改造。方法肯定很多，自己想到的就是一个用java开源的分布式框架来做，但是有点大材小用的感觉。于是就考虑对表进行分区来提高查询速度。以下就是本人对表分区过程的介绍及其遇到的各种问题的解决方式，希望对大家有所帮助!
对于表分区可以通过重建表，交换分区和在线重定义的方式来实现
具体参考http://blog.itpub.net/post/468/13091
我采用最保守的重建表的方式来实现
首先是创建表空间
1 /*1.创建表空间*/ --autoextend on 2 CREATE TABLESPACE CPU_201112 3 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201112.DBF ' SIZE 100M REUSE; 4 /*CREATE TABLESPACE CPU_201201 5 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201201.DBF ' SIZE 100M REUSE; */ 6 CREATE TABLESPACE CPU_201202 7 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201202.DBF ' SIZE 100M REUSE; 8 CREATE TABLESPACE CPU_201203 9 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201203.DBF ' SIZE 100M REUSE; 10 CREATE TABLESPACE CPU_201204 11 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201204.DBF ' SIZE 100M REUSE; 12 CREATE TABLESPACE CPU_201205 13 DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201205.DBF ' SIZE 100M REUSE;
查询创建的表空间
select * from user_tablespaces
Ps:
这里可能会遇到ora-01119的错误
原因是表空间创建的路径必须要正确
/*2.删除表空间*/
--DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;--推荐删除contents 然后在手动删除datafiles
删除表空间不是都可以的哦 亲，
造成这种原因基本是这个分区的数据包含了别的分区的数据
3.创建表分区
1 create table host_cpu_new partition by range(intime) 2 3 (partition p1 values less than(to_date('2011-12-30','yyyy-MM-dd')) TABLESPACE CPU_201112, 4 5 -- partition p2 values less than(to_date('2012-01-1','yyyy-MM-dd')) TABLESPACE CPU_201201, 6 7 partition p3 values less than(to_date('2012-02-1','yyyy-MM-dd')) TABLESPACE CPU_201202, 8 9 partition p4 values less than(to_date('2012-03-1','yyyy-MM-dd')) TABLESPACE CPU_201203, 10 11 partition p5 values less than(to_date('2012-04-1','yyyy-MM-dd')) TABLESPACE CPU_201204, 12 13 partition p6 values less than(to_date('2012-05-1','yyyy-MM-dd')) TABLESPACE CPU_201205 14 15 )as select * from host_cpu
由于目前数据只到4四月份 且11年的数据不多
所以分区创建的思路就是将11年的数据全放到一个分区 基本也不会用到
12的数据按月来分区
可是因为一时的疏忽 注意12月份可是有31的天 亲
而p2的分区是我后面加的
结果悲催的事情来了 这也是我为什么要写这篇文章主要的原因
当界面查询1月份的数据的时候 发现出现了12月份的数据
亲 你知道为什么吗？单独查询sql发现是不会有问题的
Select * from host_cpu_new where intime>=to_date(‘2012-01-01 00:00:00’,’yyyy-MM-dd HH24:mi:ss’)
And intime< to_date(‘2012-02-01 00:00:00’,’yyyy-MM-dd HH24:mi:ss’)
Java程序调用的时候结果出现了12月份的数据 想来
Java查询的时候查询了两个分区也就是p2和p3的数据了
后悔呀 只能把P2的分区给删除了 发现暴ora-14404的错误
也就是这个分区包含了其他分区的数据
没办法 只能重新重建一次了 当然也可以选择合并分区啦
最后的一点操作
Truncate table host_cpu
drop table host_cpu
alter table host_cpu_new rename to host_cpu;
完成操作
Ps 在去查询的时候 发现展示的时间缩短了大概2/3 嘻嘻。
由于期间遇到误删除表数据的情况 悲啊 什么情况
http://soft.chinabyte.com/database/161/12309661.shtml
如果是10g还可以这么做
select timestamp_to_scn(to_timestamp('表删除的时间','YYYY-MM-DD HH24:MI:SS')) from dual; --return scn
create table tablename as select * from tablename AS OF SCN scn(第一条SQL执行返回的scn结果)
关于更多表分区的知识
Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法 ;
http://adamxgl.blog.163.com/blog/static/29094652011117111221690/
删除表空间失败
http://topic.csdn.net/u/20100714/16/b9beb58e-0dd0-4653-9784-7fb7a71eb260.html
快速删除表空间
http://apps.hi.baidu.com/share/detail/15357973
表空间操作的简单介绍
