Oracle范围分区应用实例

发布时间:2020-05-24 08:10:28 作者:llc018198
来源:网络 阅读:386

概述

    范围分区(Range partition):就是根据表的某个字段的值,以固定的一个范围作为一个分区来划分数据。

 实例:

  1. 创建销售部门销售数据清单表:

    create table sales_list
    (order_id number(5),
    sales_name varchar2(20),
    order_amount number(10),
    order_date date
    )
    partition by range (order_date)
    (partition sales_201401 values less than(to_date('2014-02-01','yyyy-mm-dd')),
    partition sales_201402 values less than(to_date('2014-03-01','yyyy-mm-dd')),
    partition sales_201403 values less than(to_date('2014-04-01','yyyy-mm-dd')),
    partition sales_201404 values less than(to_date('2014-05-01','yyyy-mm-dd')),
    partition sales_201405 values less than(to_date('2014-06-01','yyyy-mm-dd')),
    partition sales_201406 values less than(to_date('2014-07-01','yyyy-mm-dd')),
    partition sales_201407 values less than(to_date('2014-08-01','yyyy-mm-dd')),
    partition sales_201408 values less than(to_date('2014-09-01','yyyy-mm-dd')),
    partition sales_201409 values less than(to_date('2014-10-01','yyyy-mm-dd')),
    partition sales_201410 values less than(to_date('2014-11-01','yyyy-mm-dd')),
    partition sales_201411 values less than(to_date('2014-12-01','yyyy-mm-dd')),
    partition sales_201412 values less than(to_date('2015-01-01','yyyy-mm-dd')),
    partition sales_201501 values less than(to_date('2015-02-01','yyyy-mm-dd')),
    partition sales_201502 values less than(to_date('2015-03-01','yyyy-mm-dd'))
    );

    创建了14个分区,14个段

    SQL> col partition_name format a20
    SQL> col segment_name format a20
    SQL> select segment_name,partition_name from user_segments where segment_name='SALES_LIST';

    SEGMENT_NAME      PARTITION_NAME
    -------------------- --------------------
    SALES_LIST      SALES_201401
    SALES_LIST      SALES_201402
    SALES_LIST      SALES_201403
    SALES_LIST      SALES_201404
    SALES_LIST      SALES_201405
    SALES_LIST      SALES_201406
    SALES_LIST      SALES_201407
    SALES_LIST      SALES_201408
    SALES_LIST      SALES_201409
    SALES_LIST      SALES_201410
    SALES_LIST      SALES_201411
    SALES_LIST      SALES_201412
    SALES_LIST      SALES_201501
    SALES_LIST      SALES_201502

        14 rows selected.

 

创建过程用于删除旧的分区并添加新的分区:

CREATE OR REPLACE PROCEDURE drop_add_partition AS
  v_part_name VARCHAR2(100);
  v_next_name VARCHAR2(100);
  v_over_time NUMBER;
  v_string    VARCHAR2(10);
  v_date      DATE;
BEGIN
  SELECT MIN(partition_name)
    INTO v_part_name
    FROM user_tab_partitions
   WHERE table_name = 'SALES_LIST'; --找到当前最早的分区
  SELECT MAX(partition_name)
    INTO v_next_name
    FROM user_tab_partitions
   WHERE table_name = 'SALES_LIST'; --找到当前最晚的分区
  SELECT substr(to_char(add_months(to_date(substr(v_next_name, 7, 6),
                                           'yyyy-mm'),
                                   1),
                        'yyyymmdd'),
                1,
                6)
    INTO v_next_name
    FROM dual; --拼接下一个新的分区的名称串
  SELECT round(months_between(SYSDATE,
                              to_date(substr(v_part_name, 7, 6), 'yyyy-mm')))
    INTO v_over_time
    FROM dual;
  --计算当前时间和最早分区的之间间隔的月数
  v_string := to_char(add_months(to_date(v_next_name, 'yyyy-mm'), 1),
                      'yyyy-mm');
  v_date   := to_date(substr(v_string, 1, 4) || '-' ||
                      substr(v_string, 6, 2) || '-01',
                      'yyyy-mm-dd');
  --拼接新的分区在创建的时候需要指定的截至时间点的串
  IF v_over_time > 12
  THEN
    EXECUTE IMMEDIATE 'alter table sales_list drop partition ' ||
                      v_part_name; --删除旧的分区
    EXECUTE IMMEDIATE 'alter table sales_list add partition SALES_' ||
                      REPLACE(v_next_name, '-', '') ||
                      ' values less than (''' || v_date || ''')'; --添加新的分区
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    --异常处理
    dbms_output.put_line(to_char(SQLCODE));
    dbms_output.put_line(SQLERRM);
END;

SQL> set serveroutput on
SQL> exec drop_add_partition;

PL/SQL procedure successfully completed.

SQL> select segment_name,partition_name from user_segments where segment_name='SALES_LIST';

SEGMENT_NAME      PARTITION_NAME
-------------------- --------------------
SALES_LIST      SALES_201402
SALES_LIST      SALES_201403
SALES_LIST      SALES_201404
SALES_LIST      SALES_201405
SALES_LIST      SALES_201406
SALES_LIST      SALES_201407
SALES_LIST      SALES_201408
SALES_LIST      SALES_201503
SALES_LIST      SALES_201409
SALES_LIST      SALES_201410
SALES_LIST      SALES_201411
SALES_LIST      SALES_201412
SALES_LIST      SALES_201501
SALES_LIST      SALES_201502

14 rows selected.

创建后台作业:
declare
  job number;
begin
  dbms_job.submit(job,'drop_add_partition;',sysdate,'sysdate+1');  --每天运行一次,创建这个job的时候就要执行一次
  commit;
end;

推荐阅读:
  1. Oracle提升查询性能之-简单范围分区表的创建
  2. Oracle分区交换

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle 分区 plsql

上一篇:Neo4j HA环境配置

下一篇:Juniper SSG firewall Packet Per Second Rate

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》