oracle11g 分区表压缩,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