海量数据优化_分区索引与删除的关系

案例： 有一个项目团队发现他们的项目上线后会在上午的时候接到用户投诉查询缓慢但并不阻塞只不过时间比正常时慢很多
经过对数据库进行分析发现这个时间的数据库资源使用非常严重, CPU的使用率达到了80%,但此时数据库的压力并不大,通过进一步的分析发现数据库中运行了大量的这样的语句:

delete from t_name where j_time<to_date( '2016-04-04','yyyy-mm-dd');

通过与开发单位联系知道他们有一个定时任务在凌晨2点后开始执行,删除1个月前的数据以便释放空间.
此时一个月差不多有1000W条数据这样删除的开销非常的大。

解决方案 : 可以使用分区来解决这个问题. 设定保留历史数据的规则是: 删除最早一个月的数据。 按照这个需求，将需要删除的数据的表修改为分区表,
按照每个月一个分区的方式来创建分区 这样删除操作就变成了删除分区了

下面是我写的具体的代码：

• 新建一个表info,并进行分区操作

create table info（
    sid number(10) primary key,
    sname varchar2(100),
    amount number(10),
    sdate date
）partition by range(sdate)(
    partition info_2016_03 values less than(to_date('2016-04-01','yyyy-mm-dd')),
    partition info_2016_04 values less than(to_date('2016-05-01','yyyy-mm-dd')),
    partition info_2016_05 values less than(to_date('2016-06-01','yyyy-mm-dd')),
    partition info_2016_06 values less than(to_date('2016-07-01','yyyy-mm-dd')),
    partition info_2016_07 values less than(to_date('2016-08-01','yyyy-mm-dd')),
    partition info_2016_08 values less than(to_date('2016-09-01','yyyy-mm-dd')),
    partition info_2016_09 values less than(to_date('2016-10-01','yyyy-mm-dd'))
);

• 新建一个序列

create sequence seq_info_sid start with 1;

truncate table info;

• 往表中写入数据

insert into info
    select seq_info_sid.nextval,'yc'||trunc(dbms_random.value(0,100)),
    round(dbms_random.value(0,9999)),add_months(sysdate,-dbms_random.value(0,7))+dbms_random.value(100,999999)/24/3600
    from dual connect by level<=2000000;

• 查询指定的分区

select *from info partition(info_2016_06);

此案例的相关操作：

• 统计info表中的数据，总共占了多少个数据块

select count(*)from user_extents where seqment_name='INFO';

• 通过delete方法删除数据时，并不会释放空间

delete from info where sid<=4070566+200000;

• 通过truncate截断分区时，会释放数据所占有的空间

alter table info truncate partition info_2016_06;

• 创建存储过程自动创建表分区

create or replace procedure por_createpartition as
    v_date varchar2(20);
    v_partitionName varchar2(30);
    v_number number(2);
    v_sql varchar2(200);
    v_dates varchar2(20);
begin
    --这个月应该就下一个月的分区创建好
    select to_char(add_months(sysdate,1),'yyyy_mm'),to_char(add_months(sysdate,2),'yyyy-mm') into v_date,v_dates from dual;
    --获取分区名
    v_partitionName :='INFO_'||v_date;
    --判断这个分区是否存在
    select count(table_name) into v_number from user_tab_partitions where table_name='INFO' and partition_name=v_partitionName;
    if v_number<=0 then --说明这个分区不存在，则可以创建
        v_sql :='alter table info add partition '||v_partitionName||' values less than(to_date('''||v_dates||''',''yyyy-mm''))';
        dbms_output.put_line(v_sql);
        execute immediate v_sql;
        dbms_output.put_line('表空间'||v_partitionName||'创建成功....');
        end if;
exception
    when others then
        dbms_output.put_line('表空间'||v_partitionName||'创建失败....');
end;