In Oracle 11g wurde das Interval Partitioning eingeführt, welches auf Range Partitioning basiert, allerdings werden die Partitionen automatisch auf Basis eines Intervals angelegt.

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 TABLE MESSWERTE
(
    ID NUMBER,
    WERT NUMBER(10,2),
    ERSTELLUNGSDATUM DATE
)
PARTITION BY RANGE(ERSTELLUNGSDATUM)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) STORE IN (TBSMW1,TBSMW2,TBSMW3)
(
 PARTITION MWP1 VALUES LESS THAN (TO_DATE('01.01.2006','DD.MM.YYYY')) TABLESPACE TBSMW1,
 PARTITION MWP2 VALUES LESS THAN (TO_DATE('01.01.2007','DD.MM.YYYY')) TABLESPACE TBSMW2,
 PARTITION MWP3 VALUES LESS THAN (TO_DATE('01.01.2008','DD.MM.YYYY')) TABLESPACE TBSMW3
);

PAUSE

INSERT INTO MESSWERTE VALUES (1,'4711',TO_DATE('01.01.2005','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (2,'4712',TO_DATE('01.01.2006','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (3,'4713',TO_DATE('01.01.2007','DD.MM.YYYY'));

SELECT PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='MESSWERTE' ORDER BY PARTITION_NAME;

PAUSE

INSERT INTO MESSWERTE VALUES (4,'4714',TO_DATE('01.01.2008','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (5,'4715',TO_DATE('01.01.2009','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (6,'4716',TO_DATE('01.01.2010','DD.MM.YYYY'));
COMMIT;

SELECT PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='MESSWERTE' ORDER BY PARTITION_NAME;

PAUSE

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;

 

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;