oracle-partition
Oracle分区
在开发过程中，我们经常会遇到某一个表的数据量超大，例如发送短信平台中，发送短信表的数据量久而久之就会特别大，直接导致查询满，虽然我们可以合理的使用索引，优化字段长度等手段，但难免在数据量很大的情况下，这些方法起到的作用往往不是很大，这时候就到分区，我们把各个时间段的短信都分成一个区，查询的时候直接查询这个分区中的数据，这样无疑就快了很多。
首先呢，分区并不是就一种范围分区，还包括哈希分区和列表分区，接下来我们先看看范围分区，最简单的一种分区，就以短信表sms_sms表作为演示
我们来建立表和表分区,这里只做演示，数据表不详细介绍
create table sms_sms(
sms_id number(5),
sms_content varchar2(30),
sms_phone number(10),
sms_date date
)
partition by range(sms_date)
(
partition sms_date_2012_1 values less than (to_date('20120201','yyyymmdd')),
partition sms_date_2012_2 values less than (to_date('20120202','yyyymmdd')),
partition sms_date_2012_3 values less than (to_date('20120203','yyyymmdd')),
partition sms_date_2012_4 values less than (to_date('20120204','yyyymmdd')),
partition sms_date_2012_5 values less than (to_date('20120205','yyyymmdd')),
partition sms_date_2012_6 values less than (to_date('20120206','yyyymmdd')),
partition sms_date_2012_7 values less than (to_date('20120207','yyyymmdd'))
);
数据表和分区建立后，我们在查询2012年1月份的时候，就会直接去sms_date_2012_1分区中查询，不会直接去扫描全表就行筛选了。
接下来，我们就会发现，我们给客户发送的2012年1月份的数据对我们来说根本就没有任何用了，那我们何必不删除它们呢，还能给我们节省磁盘空间。
创建一个存储过程来删除1年前的发送记录，并且使用job来定时执行
--创建存储过程，自动删除一年以前的旧数据
create or replace procedure drop_partition as
v_part_name varchar2(100); --要添加分区表的名称前缀
v_over_time number; --过期时间间隔
v_err_num number; --ora错误号
v_err_msg varchar2(100); --错误描述
begin
--得到时间最早的分区
select min(partition_name)
into v_part_name
from user_tab_partitions
where table_name = 'sms_sms';
--取得时间最早的分区到当前的时间间隔
select months_between(sysdate,
to_date(substr(min(partition_name), 7, 8), 'yyyy-mm'))
into v_over_time
from user_tab_partitions
where table_name = 'sms_sms';
--删除大于12个月份的分区
if v_over_time > 12 then
execute idmediate 'alter table sms_sms drop partition ' || v_part_name;
end if;
exception
when others then
v_err_num := sqlcode;
v_err_msg := substr(sqlerrm, 1, 100);
dbms_output.put_line('执行错误：' || v_err_num || '错误描述：' || v_err_msg);
end;
创建一个job来定时执行这个存储过程
declare
job number;
begin
dbms_job.submit(job, 'drop_partition;', sysdate, 'sysdate+1');
end;
