Oracle11g新增参考分区

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

复制