Reference Partitioning in Oracle 11g

In Oracle 11g wurde das Reference Partitioning eingeführt, mit der eine Fremdschlüsseltabelle auf Basis der Primärschlüsseltabelle partitioniert werden kann. Wichtig hierbei ist, dass die Fremdschlüsselspalte eine NOT NUL-Spalte ist.


CONN / AS SYSDBA

SET LINESIZE 500
SET ECHO ON
CL SCR
CREATE TABLESPACE TBSMW1 DATAFILE 'TBSMW1_01.DBF' SIZE 1M;
CREATE TABLESPACE TBSMW2 DATAFILE 'TBSMW2_01.DBF' SIZE 1M;
CREATE TABLESPACE TBSMW3 DATAFILE 'TBSMW3_01.DBF' SIZE 1M;
CREATE TABLESPACE TBSMW4 DATAFILE 'TBSMW4_01.DBF' SIZE 1M;

CREATE TABLE MESSWERTE
(
    ID NUMBER,
    TESTOBJEKT VARCHAR2(20),
    ERSTELLUNGSDATUM DATE,
    CONSTRAINT PK_MESSWERT PRIMARY KEY(ID)
)
PARTITION BY RANGE(ERSTELLUNGSDATUM)
(
    PARTITION PL2007 VALUES LESS THAN (TO_DATE('01.01.2007','DD.MM.YYYY')) TABLESPACE TBSMW1,
    PARTITION PL2008 VALUES LESS THAN (TO_DATE('01.01.2008','DD.MM.YYYY')) TABLESPACE TBSMW2,
    PARTITION PL2009 VALUES LESS THAN (TO_DATE('01.01.2009','DD.MM.YYYY')) TABLESPACE TBSMW3,
    PARTITION PL2010 VALUES LESS THAN (TO_DATE('01.01.2010','DD.MM.YYYY')) TABLESPACE TBSMW4
);

PAUSE

CREATE TABLE MESSWERTEINTRAG
(
    WERT NUMBER(10,2),
    ID_MESSWERTE NUMBER NOT NULL,
    CONSTRAINT FK_MESSWERTE FOREIGN KEY(ID_MESSWERTE) REFERENCES MESSWERTE(ID)
)
PARTITION BY REFERENCE(FK_MESSWERTE);

PAUSE

SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME, HIGH_VALUE
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME IN ('MESSWERTE','MESSWERTEINTRAG') ORDER BY TABLE_NAME,PARTITION_NAME;

PAUSE

DROP TABLE MESSWERTEINTRAG PURGE;
DROP TABLE MESSWERTE PURGE;
DROP TABLESPACE TBSMW1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBSMW2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBSMW3 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBSMW4 INCLUDING CONTENTS AND DATAFILES;