创建表分区的总结

最近接手一个需求，需要展示主机的性能值(指主机的CPU利用率)主机60台,每台10分钟产生条记录。每天数据量60*6*24=8640 一年300W条记录 结合一些业务要求 展示数据时间大概要10秒左右。不符合顾客需要，所以就需要进行优化改造。方法肯定很多，自己想到的就是一个用java开源的分布式框架来做，但是有点大材小用的感觉。于是就考虑对表进行分区来提高查询速度。以下就是本人对表分区过程的介绍及其遇到的各种问题的解决方式，希望对大家有所帮助!

对于表分区可以通过重建表，交换分区和在线重定义的方式来实现

具体参考http://blog.itpub.net/post/468/13091

我采用最保守的重建表的方式来实现

首先是创建表空间

/*1.创建表空间*/ --autoextend on
CREATE TABLESPACE CPU_201112 
       DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201112.DBF ' SIZE 100M REUSE;
/*CREATE TABLESPACE CPU_201201 
       DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201201.DBF ' SIZE 100M REUSE;  */
CREATE TABLESPACE CPU_201202 
       DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201202.DBF ' SIZE 100M REUSE;  
CREATE TABLESPACE CPU_201203 
       DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201203.DBF ' SIZE 100M REUSE;  
CREATE TABLESPACE CPU_201204 
       DATAFILE 'F:\ORACLE\ORADATA\NGNMC\CPU_201204.DBF ' SIZE 100M REUSE;        
CREATE TABLESPACE CPU_201205 
       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.创建表分区           

create  table   host_cpu_new  partition by range(intime)

 (partition p1 values less than(to_date('2011-12-30','yyyy-MM-dd')) TABLESPACE CPU_201112,

-- partition p2 values less than(to_date('2012-01-1','yyyy-MM-dd')) TABLESPACE CPU_201201,

 partition p3 values less than(to_date('2012-02-1','yyyy-MM-dd')) TABLESPACE CPU_201202,

 partition p4 values less than(to_date('2012-03-1','yyyy-MM-dd')) TABLESPACE CPU_201203,

 partition p5 values less than(to_date('2012-04-1','yyyy-MM-dd')) TABLESPACE CPU_201204,

  partition p6 values less than(to_date('2012-05-1','yyyy-MM-dd')) TABLESPACE CPU_201205

 )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

表空间操作的简单介绍

http://blog.sina.com.cn/s/blog_53d1a7710100072u.html