Virtual Column Partitioning

Ab Oracle 11g kann eine Tabelle auf Basis einer virtuellen Spalte partitioniert werden. Ab Oracle 11g kann eine Tabelle auf Basis einer virtuellen Spalte partitioniert werden.

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,
 WERT NUMBER(10,2),
 ERSTELLUNGSDATUM DATE,
 QUART AS (TO_NUMBER(TO_CHAR(ERSTELLUNGSDATUM,'Q')))
)
PARTITION BY LIST(QUART)
(
 PARTITION QUART1 VALUES(1) TABLESPACE TBSMW1,
 PARTITION QUART2 VALUES(2) TABLESPACE TBSMW2,
 PARTITION QUART3 VALUES(3) TABLESPACE TBSMW3,
 PARTITION QUART4 VALUES(4) TABLESPACE TBSMW4
);

PAUSE

INSERT INTO MESSWERTE(ID,WERT,ERSTELLUNGSDATUM) VALUES (1,'4711',TO_DATE('01.01.2009','DD.MM.YYYY'));
INSERT INTO MESSWERTE(ID,WERT,ERSTELLUNGSDATUM) VALUES (2,'4712',TO_DATE('01.04.2009','DD.MM.YYYY'));
INSERT INTO MESSWERTE(ID,WERT,ERSTELLUNGSDATUM) VALUES (3,'4713',TO_DATE('01.07.2009','DD.MM.YYYY'));
INSERT INTO MESSWERTE(ID,WERT,ERSTELLUNGSDATUM) VALUES (4,'4714',TO_DATE('01.10.2009','DD.MM.YYYY'));
COMMIT;

PAUSE

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

PAUSE

SELECT * FROM MESSWERTE;
PAUSE
SELECT * FROM MESSWERTE PARTITION(QUART1) WHERE QUART=1;
PAUSE
SELECT * FROM MESSWERTE PARTITION(QUART1) WHERE QUART=2;
PAUSE
SELECT * FROM MESSWERTE WHERE QUART=2;

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;