11g 新特性之自动分区-numtoyminterval/numtodsinterval

The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
一、按月分区
1、创建自动分区表，必须指定至少一个默认的分区名称，后面的分区名称Oracle会自动创建，测试如下：
JZH@jzh>create table interval_sales (
  2  prod_id number(6),
  3  time_id date)
  4  partition by range (time_id)
  5  INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))--------可以是MONTH,YEAR,DAY
  6  (partition p1 values less than (to_date('2015-01-01','yyyy-mm-dd')));

Table created.
2、查看分区
JZH@jzh>select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_SALES                 P1
当前只有一个P1默认分区
3、插入数据
JZH@jzh>insert into interval_sales values(001,to_date('2015-02-01','yyyy-mm-dd'));
1 row created.
JZH@jzh>commit;
Commit complete.

4、再次查看分区
JZH@jzh>select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
INTERVAL_SALES                 P1
INTERVAL_SALES                 SYS_P61

可以看到多了一个名称为SYS_P61的分区，我们插入的数据是2015-02-01，应该存放在SYS_P61分区。
5、查看SYSP_61分区数据
JZH@jzh>select * from interval_sales partition(sys_p61);
   PROD_ID TIME_ID
---------- ---------
         1 01-FEB-15

二、按天分区
1、创建测试表
JZH@jzh>create table day_partition (prod_id number(6),time_id date)
  2  partition by range(time_id)
  3  interval (numtodsinterval(1,'DAY'))
  4  (partition p1 values less than(to_date('2015-08-07','yyyy-mm-dd')));
Table created.
2、插入数据
JZH@jzh>insert into day_partition  values(001,to_date('2015-08-08','yyyy-mm-dd'));
1 row created.
JZH@jzh>commit;
Commit complete.
3、查看分区
JZH@jzh>select table_name,partition_name from user_tab_partitions where table_name='DAY_PARTITION';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
DAY_PARTITION                  P1
DAY_PARTITION                  SYS_P63
可以看到多了一个SYS_P63分区，接下来查看一下2015-08-08是否在SYS_P63分区上
4、查看分区数据
JZH@jzh>select * from day_partition partition(sys_p63);
   PROD_ID TIME_ID
---------- ---------
         1 08-AUG-15
三、按年分区
1、创建测试表
JZH@jzh>create table year_partition (prod_id number(6),time_id date)
  2  partition by range(time_id)
  3  interval (numtoyminterval(1,'YEAR'))
  4  (partition p1 values less than(to_date('2014-01-01','yyyy-mm-dd')));
Table created.
2、插入数据
JZH@jzh>insert into year_partition values(001,to_date('2015-01-01','yyyy-mm-dd'));
1 row created.
JZH@jzh>commit;
Commit complete.
3、查看分区
JZH@jzh>select table_name,partition_name from user_tab_partitions where table_name='YEAR_PARTITION';
TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
YEAR_PARTITION                 P1
YEAR_PARTITION                 SYS_P64
可以看到多了一个SYS_P64分区，接下来查看一下2015-01-01是否在SYS_P64分区上
4、查看分区数据
JZH@jzh>select * from year_partition partition(sys_p64);
   PROD_ID TIME_ID
---------- ---------
         1 01-JAN-15

总结：需要注的是，按年，月分区函数是 numtoyminterval，而按天分区函数是numtodsinterval。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10271187/viewspace-1765908/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10271187/viewspace-1765908/