Oracle11g 新特性之間隔分割槽運用說明

一、概述

間隔分割槽（interval partitioning ）是oracle database 11g Release 1 及以上版本中的新增特性，它就是以一個區間分割槽表為“起點”，並在定義中增加了一個規則（即間隔），使資料庫知道將來如何增加分割槽。
目標：當且僅當存在一個給定分割槽的資料而且這些資料加入資料庫時才為資料建立新的分割槽。簡單來講，就是不需要預先為資料建立分割槽，而是在插入資料時讓資料自己建立分割槽。要使用間隔分割槽，首先從一個沒有MAXVALUE 分割槽的區間分割槽表開始，指定一個要增加至上界(upper bound) 的間隔（上界是最大值，如果達到這個最大值，分割槽表就要建立一個新的區間分割槽）。這個分割槽表要按某一列分割槽，而且這一列能夠增加number 或者interval 型別的值（例如，按varchar2 欄位分割槽的分割槽表就不能使用間隔分割槽，也就是說，可以使用alter 命令將現有的區間分割槽表修改為間隔分割槽，也可以使用create 建立一個間隔分割槽。

二、操作說明

建立一個使用者，並建立間隔分割槽

SQL> create tablespace xysoul datafile '/oracle11g/product/11.2.0/oradata/loves/xysoul01.dbf' size 1G;

Tablespace created.

SQL> conn xysoul/xysoul

Connected.

SQL> create table audit_trail (ts timestamp,data varchar2(30))

2 partition by range(ts)

3 interval (numtoyminterval(1,'month'))

4 store in (users,xysoul)

5 (partition p0 values less than

6 (to_date('2010-01-01','yyyy-mm-dd')));

Table created.

注意：如果按月份間隔，不能寫某個月28 日以後的某一天，由於2 月沒有31 日。如果填寫，報錯如下：

SQL> create table audit_trail1 (ts timestamp,data varchar2(30))

2 partition by range(ts)

3 interval (numtoyminterval(1,'month'))

4 store in (users,xysoul)

5 (partition p0 values less than

6 (to_date('2010-01-29','yyyy-mm-dd')));

create table audit_trail1 (ts timestamp,data varchar2(30))

*

ERROR at line 1:

ORA-14767: Cannot specify this interval with existing high bounds

檢視資料字典，檢視分割槽資訊

SQL> select a.partition_name,a.tablespace_name,a.high_value,

2 decode(a.interval,'YES',b.interval) invertal

3 from user_tab_partitions a,user_part_tables b

4 where a.table_name='AUDIT_TRAIL'

5 and a.table_name=b.table_name

6 order by a.partition_position;
PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL

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

P0 USERS TIMESTAMP' 2010-01-01 00:00:00'

插入一條資料，並檢視，發現多了一個分割槽

SQL> insert into audit_trail(ts,data) values

2 (to_timestamp('2010-03-27','yyyy-mm-dd'),'xysoul');

SQL> col INVERTAL for a40

SQL> col HIGH_VALUE for a35

SQL> set lines 999

SQL> col tablespace_name for a20

SQL> col PARTITION_NAME for a15

SQL> select a.partition_name,a.tablespace_name,a.high_value,

2 decode(a.interval,'YES',b.interval) invertal

3 from user_tab_partitions a,user_part_tables b

4 where a.table_name='AUDIT_TRAIL'

5 and a.table_name=b.table_name

6 order by a.partition_position;

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL

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

P0 USERS TIMESTAMP' 2010-01-01 00:00:00'

SYS_P61 XYSOUL TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

再次插入一條資料，並檢視

SQL> insert into audit_trail(ts,data) values

2 (to_timestamp('2010-07-7','yyyy-mm-dd'),'lss');

1 row created.

SQL>

SQL> col INVERTAL for a40

SQL> col HIGH_VALUE for a35

SQL> set lines 999

SQL> col tablespace_name for a20

SQL> col PARTITION_NAME for a15

SQL> select a.partition_name,a.tablespace_name,a.high_value,

2 decode(a.interval,'YES',b.interval) invertal

3 from user_tab_partitions a,user_part_tables b

4 where a.table_name='AUDIT_TRAIL'

5 and a.table_name=b.table_name

6 order by a.partition_position;

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL

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

P0 USERS TIMESTAMP' 2010-01-01 00:00:00'

SYS_P61 XYSOUL TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P62 XYSOUL TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

我們發現，現在使用的表空間都為XYSOUL ，再次插入兩條資料，看一下

SQL> insert into audit_trail(ts,data) values

2 (to_timestamp('2010-02-22','yyyy-mm-dd'),'lss');

1 row created.

SQL> col INVERTAL for a40

SQL> col HIGH_VALUE for a35

SQL> set lines 999

SQL> col tablespace_name for a20

SQL> col PARTITION_NAME for a15

SQL> select a.partition_name,a.tablespace_name,a.high_value,

2 decode(a.interval,'YES',b.interval) invertal

3 from user_tab_partitions a,user_part_tables b

4 where a.table_name='AUDIT_TRAIL'

5 and a.table_name=b.table_name

6 order by a.partition_position;

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL

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

P0 USERS TIMESTAMP' 2010-01-01 00:00:00'

SYS_P64 USERS TIMESTAMP' 2010-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P61 XYSOUL TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P63 XYSOUL TIMESTAMP' 2010-06-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P62 XYSOUL TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

就像我們看到的，偶數（按月份）分割槽存放於表空間XYSOUL ，奇數分割槽存放於表空間USERS 中。

檢視錶中資料，並執行回滾操作，再次檢視錶及分割槽資訊

SQL> select * from audit_trail;

TS DATA

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

22-FEB-10 12.00.00.000000 AM lss

27-MAR-10 12.00.00.000000 AM xysoul

12-MAY-10 12.00.00.000000 AM lss

07-JUL-10 12.00.00.000000 AM lss

SQL> rollback;

Rollback complete.

SQL> select * from audit_trail;

no rows selected

SQL> col INVERTAL for a40

SQL> col HIGH_VALUE for a35

SQL> set lines 999

SQL> col tablespace_name for a20

SQL> col PARTITION_NAME for a15

SQL> select a.partition_name,a.tablespace_name,a.high_value,

2 decode(a.interval,'YES',b.interval) invertal

3 from user_tab_partitions a,user_part_tables b

4 where a.table_name='AUDIT_TRAIL'

5 and a.table_name=b.table_name

6 order by a.partition_position;

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE INVERTAL

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

P0 USERS TIMESTAMP' 2010-01-01 00:00:00'

SYS_P64 USERS TIMESTAMP' 2010-03-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P61 XYSOUL TIMESTAMP' 2010-04-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P63 XYSOUL TIMESTAMP' 2010-06-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

SYS_P62 XYSOUL TIMESTAMP' 2010-08-01 00:00:00' NUMTOYMINTERVAL(1,'MONTH')

看以上資訊得知，執行回滾後，表中資料消失，而分割槽還在。由於這些分割槽使用一個遞迴事務（recursive transaction) 來建立，遞迴事務是從正在執行的事務之外單獨執行的事務。插入資料行時，資料庫發現我們需要的分割槽尚不存在，就會立即開始一個新的事務，更新資料字典來反映這個新分割槽的存在，並提交所做工作。它必須這樣做，否則存在多個插入會出現嚴重的競爭（序列化），因為其他事務必須等待我們提交才能看到這個新分割槽。因此，這個DDL 是在現有事務之外完成的，所以分割槽會持久保留。

順便提一句，儲存AWR 資訊的幾個檢視中，也是自動建立分割槽，包括oracle10g ，至今沒整明白到底根據什麼分割槽的，一般一個分割槽表裡24 個快照資訊，詳細說明可參考其他博文，如有知道的同志請告知在下，不勝感激。

三、總結

對於資料庫還是基礎不是太牢固，有些浮躁吧，還不能完全沉下心來靜靜的讀、寫東西，我們總是盲目的去做實驗，或者呆呆的看文件，希望兩者能夠結合的更好，不能只紙上談兵，也不能就當一個武將，加油吧。參考《程式設計藝術》

Xysoul_ 雲龍 2014 年2 月20 日星期四 22:50

部落格地址：http://blog.sina.com.cn/longzhimeng99

【PARTITION】Oracle11g新特性之間隔分割槽運用說明

相關文章