11g分割槽新特性之interval partition
什麼是interval partition
11g推出的一項新特性,可以自動為範圍分割槽表新增分割槽;一旦採用此功能,就不可手工新增分割槽了;
可以為interval partition指定多個表空間,oracle採用輪循的方式挨個使用每個表空間;
如何使用interval partition
測試環境11201
如下建立一個測試表
create table sales
(
sales_id number,
sales_dt date
)
partition by range (sales_dt)
(
partition p1107 values less than (to_date('2012-08-01','yyyy-mm-dd'))
);
如果試圖插入12年8月1號以後的資料,則會報錯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
--先插入12年9月的資料,自動分配的分割槽名為sys_p21,如此時再插入12年8月的資料,對應的分割槽名則為sys_p22
--因此使用interval分割槽時,最好確保插入的資料是有序的,否則可能造成如上不便
管理維護interval-partitioned表
對於現有的interval-partitioned表,可以使用set interval()將其改回range-partitioned表;
也可使用set interval(
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 11g分佈表新特性——Interval分割槽(上)
- 11g分佈表新特性——Interval分割槽(下)
- Oracle 11g的新特性分割槽:System PartitionOracle
- 【ORACLE新特性】11G 分割槽新特性Oracle
- [引用分割槽表]Oracle 11g新特性之引用分割槽表Oracle
- 定期truncate 歷史間隔分割槽INTERVAL PARTITION
- Oracle Interval Partition 自動分割槽表-實驗Oracle
- 11g 新特性之自動分割槽-numtoyminterval/numtodsinterval
- Oracle查詢Interval partition分割槽表內資料Oracle
- Oracle Interval Partition 預設分割槽重新命名-指令碼Oracle指令碼
- 【PARTITION】Oracle11g新特性之間隔分割槽運用說明Oracle
- Oracle12c分割槽新特性之TRUNCATEPARTITION和EXCHANGE PARTITION級聯功能Oracle
- ORACLE 11g 範圍分割槽錶轉換INTERVAL分割槽表Oracle
- INTERVAL分割槽表鎖分割槽操作
- Interval Partition table 11G
- 分割槽Partition
- 使用Oracle Database 11g建立Interval分割槽表OracleDatabase
- zt_11g新特性 ——更加靈活的分割槽策略partition
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- DATE型別INTERVAL分割槽型別
- 11g新特性--基於虛擬列的分割槽
- Oracle11g INTERVAL分割槽新增分割槽策略Oracle
- interval partition自動新增分割槽引起的shared pool 4031錯誤
- 【實驗】【PARTITION】RANGE分割槽表截斷表分割槽(Truncate Partition)
- 【實驗】【PARTITION】RANGE分割槽表移動表分割槽(Move Partition)
- 分割槽表PARTITION table
- ORACLE 19c 新特性之混合分割槽表Oracle
- ORACLE 範圍分割槽 partition-range分割槽Oracle
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(二)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(三)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(四)Oracle索引Index
- 【實驗】【PARTITION】RANGE分割槽表重新命名錶分割槽(Rename Partition)
- Oracle 12C 新特性之表分割槽或子分割槽的線上遷移Oracle
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- 【實驗】【PARTITION】RANGE分割槽表合併分割槽
- 【實驗】【PARTITION】RANGE分割槽表增加分割槽
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽