Oracle11新特性——分割槽功能增強

Oracle11g在分割槽方面做了很大的提高，不但新增了4種複合分割槽型別，還增加了虛擬列分割槽、系統分割槽、INTERVAL分割槽等功能。

這一篇介紹Oracle11g新增的參考分割槽功能。

Oracle11新特性——分割槽功能增強（一）：http://yangtingkun.itpub.net/post/468/403962

Oracle11新特性——分割槽功能增強（二）：http://yangtingkun.itpub.net/post/468/404223

11增加了參考分割槽功能，對於主子表關係，如果對主表進行了分割槽，那麼可以在子表上根據外來鍵約束來建立對應主表的分割槽。

這樣主表和子表採用相同的等同分割槽方式，不但連線的時候可以利用PARTITION-WISE JOIN，而且對於主子表的分割槽操作也會十分方便。

而且，這種方式並不需要在子表中存在主表的分割槽列。

SQL> CREATE TABLE T_PRIMARY 
2 (
3 OWNER, 
4 TABLE_NAME, 
5 TABLESPACE_NAME, 
6 STATUS, 
7 CONSTRAINT PK_T_PRIMARY PRIMARY KEY (OWNER, TABLE_NAME)
8 )
9 PARTITION BY LIST (TABLESPACE_NAME)
10 (
11 PARTITION P1 VALUES ('SYSTEM'), 
12 PARTITION P2 VALUES ('YANGTK'), 
13 PARTITION P3 VALUES ('SYSAUX'), 
14 PARTITION P4 VALUES (DEFAULT)
15 )
16 AS SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS FROM DBA_TABLES;

表已建立。

SQL> CREATE TABLE T_FOREIGN 
2 (
3 OWNER VARCHAR2(30) NOT NULL, 
4 TABLE_NAME VARCHAR2(30) NOT NULL, 
5 PARTITION_NAME VARCHAR2(30), 
6 SUBPARTITION_NAME VARCHAR2(30), 
7 NUM_ROWS NUMBER, 
8 BLOCKS NUMBER,
9 CONSTRAINT FK_T_FOREIGN FOREIGN KEY (OWNER, TABLE_NAME) 
10 REFERENCES T_PRIMARY (OWNER, TABLE_NAME)
11 )
12 PARTITION BY REFERENCE (FK_T_FOREIGN);

表已建立。

這就是一個簡單的例子，需要注意，對於PARTITION BY REFERENCE要求子表的外來鍵約束列必須設定NOT NULL約束。

在插入子表資料時，經常可能出現下面的錯誤：

SQL> INSERT INTO T_FOREIGN 
2 SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, NUM_ROWS, BLOCKS 
3 FROM DBA_TAB_STATISTICS
4 WHERE OBJECT_TYPE = 'TABLE';
INSERT INTO T_FOREIGN
*第 1 行出現錯誤:
ORA-14400: 插入的分割槽關鍵字未對映到任何分割槽

這個錯誤資訊有一定的迷惑性，主表建立分割槽的時候已經指定了DEFAULT分割槽，為什麼還會出現這個錯誤呢。其實這個錯誤的真正原有是插入的資料違反了外來鍵約束，使得Oracle無法通過外來鍵找到主表的分割槽資訊，因此報錯。

www.ixdba.net

這裡出錯是由於T_PRIMARY中沒有新建的表資訊。

SQL> DELETE T_PRIMARY;

已刪除2479行。

SQL> INSERT INTO T_PRIMARY SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS FROM DBA_TABLES;

已建立2482行。

SQL> INSERT INTO T_FOREIGN 
2 SELECT OWNER, TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, NUM_ROWS, BLOCKS 
3 FROM DBA_TAB_STATISTICS
4 WHERE OBJECT_TYPE = 'TABLE';

已建立2482行。

SQL> COMMIT;

提交完成。

檢視一下分割槽的情況：

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS
2 WHERE TABLE_NAME IN ('T_PRIMARY', 'T_FOREIGN') 
3 ORDER BY 1, 2;

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ------------------------
T_FOREIGN P1
T_FOREIGN P2
T_FOREIGN P3
T_FOREIGN P4
T_PRIMARY P1 'SYSTEM'
T_PRIMARY P2 'YANGTK'

T_PRIMARY P3 'SYSAUX'
T_PRIMARY P4 DEFAULT

已選擇8行。

最後檢查一下Oracle是否根據等同原則對子表進行分割槽：

SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM T_PRIMARY
2 WHERE TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX');

OWNER TABLE_NAME TABLESPACE_NAME
-------- ---------- ----------------SYS DUAL SYSTEM

YANGTK T YANGTK

CTXSYS DR$INDEX SYSAUX

YANGTK T_PRIMARY

SQL> SELECT B.OWNER, B.TABLE_NAME, TABLESPACE_NAME, OBJECT_NAME, SUBOBJECT_NAME 
2 FROM 
3 DBA_OBJECTS A, 
4 (
5 SELECT B.OWNER, B.TABLE_NAME, A.TABLESPACE_NAME, 
6 DBMS_ROWID.ROWID_OBJECT(B.ROWID) DATA_OBJECT_ID
7 FROM T_PRIMARY A, T_FOREIGN B
8 WHERE A.OWNER = B.OWNER
9 AND A.TABLE_NAME = B.TABLE_NAME
10 AND A.TABLE_NAME IN ('DUAL', 'T_PRIMARY', 'T', 'DR$INDEX')
11 ) B
12 WHERE A.DATA_OBJECT_ID = B.DATA_OBJECT_ID;

OWNER TABLE_NAME TABLESPACE_NAME OBJECT_NAM SUBOBJECT_NAME
-------- ---------- ---------------- ---------- ------------------------------
YANGTK T_PRIMARY T_FOREIGN P4 CTXSYS DR$INDEX SYSAUX T_FOREIGN P3
YANGTK T YANGTK T_FOREIGN P2
SYS DUAL SYSTEM T_FOREIGN P1

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