11g分割槽新特性之interval partition

myownstars發表於2012-08-02

什麼是interval partition

11g推出的一項新特性,可以自動為範圍分割槽表新增分割槽;一旦採用此功能,就不可手工新增分割槽了;

可以為interval partition指定多個表空間,oracle採用輪循的方式挨個使用每個表空間;

 

如何使用interval partition

測試環境11201

如下建立一個測試表

create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p
1107 values less than (to_date('2012-08
-01','yyyy-mm-dd'))
);

如果試圖插入1281號以後的資料,則會報錯ORA-14400: inserted partition key does not map to any partition

SQL> insert into sales values (1, to_date('2012-09-01','yyyy-mm-dd'));

insert into sales values (1, to_date('2009-09-01','yyyy-mm-dd'))

            *

ERROR at line 1:

ORA-14400: inserted partition key does not map to any partition

將其改為interval 分割槽

SQL> alter table sales set interval(NUMTOYMINTERVAL(1,'month'));

 

Table altered.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

此時表裡還沒有資料

SQL> insert into sales values (1, to_date('2012-09-01','yyyy-mm-dd'));

 

1 row created.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

 

SQL> rollback;

 

Rollback complete.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--此時資料可以成功插入該表,即便rollback後新分割槽依然存在

SQL> insert into sales values (1, to_date('2012-08-01','yyyy-mm-dd'));

 

1 row created.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--先插入129月的資料,自動分配的分割槽名為sys_p21,如此時再插入128月的資料,對應的分割槽名則為sys_p22

--因此使用interval分割槽時,最好確保插入的資料是有序的,否則可能造成如上不便

 

管理維護interval-partitioned

對於現有的interval-partitioned表,可以使用set interval()將其改回range-partitioned表;

也可使用set interval()改變其interval設定,首先將其變為range partitions,然後自動建立新的interval partitions

 

1 interval-partitioned表改回range-partitioned

SQL> select dbms_metadata.get_ddl('TABLE','SALES') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','SALES')

--------------------------------------------------------------------------------

 

  CREATE TABLE "SYS"."SALES"

   (    "SALES_ID" NUMBER,

        "SALES_DT" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("SALES_DT") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

 (PARTITION "P1107"  VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM

-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" NOCOMPRESS )

 

--將其變回range partition

SQL>  alter table sales set interval();

 

Table altered.

 

SQL> select dbms_metadata.get_ddl('TABLE','SALES') from dual;

 

DBMS_METADATA.GET_DDL('TABLE','SALES')

--------------------------------------------------------------------------------

 

  CREATE TABLE "SYS"."SALES"

   (    "SALES_ID" NUMBER,

        "SALES_DT" DATE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM"

  PARTITION BY RANGE ("SALES_DT")

 (PARTITION "P1107"  VALUES LESS THAN (TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM

-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "SYSTEM" NOCOMPRESS ,

 PARTITION "SYS_P22"  VALUES LESS THAN (TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-M

M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_

 

2 改變現有表的interval

--

SQL> alter table sales set interval(NUMTODSINTERVAL(7,'day'));

 

Table altered.

 

SQL> insert into sales values (4,to_date('2012-08-07','yyyy-mm-dd'));

 

1 row created.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

 

SQL> insert into sales values (4,to_date('2012-10-01','yyyy-mm-dd'));

 

1 row created.

 

SQL> select partition_name,high_value from dba_tab_partitions where TABLE_NAME='SALES';

 

PARTITION_NAME  HIGH_VALUE

--------------- ----------------------------------------------------------------------------------------------------

P1107           TO_DATE(' 2012-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P22         TO_DATE(' 2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P21         TO_DATE(' 2012-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P23         TO_DATE(' 2012-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--即使改變現有表的interval,以及存在的範圍分割槽也不會被修改

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-739613/,如需轉載,請註明出處,否則將追究法律責任。

相關文章