sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle分区表使用Interval和虚拟列按星期进行分区案例

时间:2017-01-02 22:18   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库分区表的文章,详细介绍了Oracle分区表使用Interval和虚拟列按星期进行分区案例。建一个按星期分区的表,就是按星期几分区,可以用Oracle 11g 中虚拟列来实现。

在11g之前 分区表的partition key必须是物理存在的。 11g开始提供了虚拟列,并且可以作为partition key 。


一.按星期分区表示例:
1. 创建分区表:
CREATE TABLE PT
(
   getdate   date NOT NULL,
   wd        NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate, 'D'))) VIRTUAL
)
PARTITION BY LIST (wd)
   (PARTITION Sun  VALUES (1),
    PARTITION Mon  VALUES (2),
    PARTITION Tue  VALUES (3),
    PARTITION Wed  VALUES (4),
    PARTITION Thu  VALUES (5),
    PARTITION Fri  VALUES (6),
PARTITION Sat   VALUES (7)
);

2. 插入测试数据
SQL> insert into pt(getdate) values(sysdate);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-1);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-2);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-3);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-4);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-5);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-6);
已创建 1 行。
SQL> insert into pt(getdate) values(sysdate-7);
已创建 1 行。
SQL> commit;
提交完成。

3. 查看数据:
SQL> select * from pt;
GETDATE                WD
-------------- ----------
06-6月 -10              1
07-6月 -10              2
08-6月 -10              3
09-6月 -10              4
10-6月 -10              5
03-6月 -10              5
04-6月 -10              6
05-6月 -10              7
已选择8行。

SQL> alter session set nls_date_format='YYYY-MM-DD';
会话已更改。

SQL> select * from pt;
GETDATE            WD
---------- ----------
2010-06-06          1
2010-06-07          2
2010-06-08          3
2010-06-09          4
2010-06-10          5
2010-06-03          5
2010-06-04          6
2010-06-05          7
已选择8行。

4. 查看每个分区里的内容:
SQL> select * from pt partition(sun);
GETDATE            WD
---------- ----------
2010-06-06          1
SQL> select * from pt partition(mon);
GETDATE            WD
---------- ----------
2010-06-07          2
SQL> select * from pt partition(tue);
GETDATE            WD
---------- ----------
2010-06-08          3
SQL> select * from pt partition(wed);
GETDATE            WD
---------- ----------
2010-06-09          4
SQL> select * from pt partition(fri);
GETDATE            WD
---------- ----------
2010-06-04          6
SQL> select * from pt partition(sat);
GETDATE            WD
---------- ----------
2010-06-05          7
SQL>
注:如果是10g,就要添加一物理列。

二. Interval分区
通过以上操作,就完成了一个按星期分区的分区表。 在Oracle Database 11g中还可以创建新类型的Interval分区表,Interval类型分区表,可以根据加载数据,自动创建指定间隔的分区。

创建按月分区的分区表:
1. 创建分区表
/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */
CREATE TABLE intervalpart (c1 NUMBER, c3 DATE)
PARTITION BY RANGE (c3)
   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
   (PARTITION part1
       VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')),
    PARTITION part2
       VALUES LESS THAN (TO_DATE ('02/12/2010', 'MM/DD/YYYY'))
  )
注意:如果在建Interval分区表是没有把所有的分区写完成,在插入相关数据后会自动生成分区

2. 查看现在表的分区:
SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART                   PART1
INTERVALPART                   PART2

3.  插入测试数据:
SQL> begin
  2  for i in 0 .. 11 loop
  3  insert into intervalpart values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));
  4  end loop ;
  5  commit;
  6  end;
  7  /
PL/SQL 过程已成功完成。

补充:add_months()函数获取前一个月或者下一个月的月份, 参数中 负数 代表 往前, 正数 代表 往后。
--上一个月
select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;
--下一个月
select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

4. 观察自动创建的分区:
SQL>  select table_name,partition_name from user_tab_partitions where table_name='INTERVALPART';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART                   PART1
INTERVALPART                   PART2
INTERVALPART                   SYS_P22
INTERVALPART                   SYS_P23
INTERVALPART                   SYS_P24
INTERVALPART                   SYS_P25
INTERVALPART                   SYS_P26
INTERVALPART                   SYS_P27
INTERVALPART                   SYS_P28
INTERVALPART                   SYS_P29
INTERVALPART                   SYS_P30
INTERVALPART                   SYS_P31
已选择12行。

5. 查看分区内容:
SQL> select * from INTERVALPART;
        C1 C3
---------- ----------
         1 2010-01-01
         0 2010-01-01
         1 2010-02-01
         2 2010-03-01
         3 2010-04-01
         4 2010-05-01
         5 2010-06-01
         6 2010-07-01
         7 2010-08-01
         8 2010-09-01
         9 2010-10-01
        10 2010-11-01
        11 2010-12-01
已选择13行。
SQL> select * from INTERVALPART partition(part1);
        C1 C3
---------- ----------
         1 2010-01-01
         0 2010-01-01
SQL> select * from INTERVALPART partition(part2);
        C1 C3
---------- ----------
         1 2010-02-01

下面创建一个以天为间隔的分区表:

1. 创建分区表:
SQL> create table dave
  2  (
  3  id    number,
  4  dt    date
  5  )
  6  partition by range (dt)
  7  INTERVAL (NUMTODSINTERVAL(1,'day'))
  8  (
  9  partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd'))
10  );

2. 查看表分区:
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DAVE                           P100101

3. 插入测试数据:
SQL> begin
  2  for i in 1 .. 12 loop
  3  insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL 过程已成功完成。

4. 观察自动创建的分区:
SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DAVE                           P100101
DAVE                           SYS_P32
DAVE                           SYS_P33
DAVE                           SYS_P34
DAVE                           SYS_P35
DAVE                           SYS_P36
DAVE                           SYS_P37
DAVE                           SYS_P38
DAVE                           SYS_P39
DAVE                           SYS_P40
DAVE                           SYS_P41
DAVE                           SYS_P42
DAVE                           SYS_P43
已选择13行。

5. 查看分区内容:
SQL> select * from dave partition(SYS_P32);
        ID DT
---------- ----------
         1 2010-01-02
SQL> select * from dave partition(SYS_P33);
        ID DT
---------- ----------
         2 2010-01-03
SQL> select * from dave partition(SYS_P34);
        ID DT
---------- ----------
         3 2010-01-04
SQL> select * from dave;
        ID DT
---------- ----------
         1 2010-01-02
         2 2010-01-03
         3 2010-01-04
         4 2010-01-05
         5 2010-01-06
         6 2010-01-07
         7 2010-01-08
         8 2010-01-09
         9 2010-01-10
        10 2010-01-11
        11 2010-01-12
        12 2010-01-13
已选择12行。

我们对Interval分区 进行一个总结:通过上面的2个例子我们发现一个现象,就是在创建分区的时候,如果选择了Interval分区,那么在该分区表中,没有创建的分区会自动生成。 这样有什么好处呢?
我们假设一下,由于数据量的巨大,所以表设计为每天一个分区,数据库管理员日常要做的一件重复而无聊的工作就是每隔一天要生成新的分区,用以存储第二天的数据。如果在Oracle 11g,我们就可以用Interval分区很好的实现这个功能,Oracle会自动完成剩下的分区,是不是很方便呢?

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle分区表使用Interval和虚拟列按星期进行分区案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1441.html

Oracle研究中心

关键词:

Oracle分区表

oracle Interval分区表

oracle 虚拟列分区表

oracle分区表以星期条件进行