Oracle11新特性——分区功能增强（三）

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无法通过外键找到主表的分区信息，因此报错。

这里出错是由于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