11g 新特性之自動分割槽-numtoyminterval/numtodsinterval

parknkjun發表於2015-08-09
原文網址 : http://blog.itpub.net/10271187/viewspace-1765908/
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章