INTERVAL-LIST Composite Partitioning

ORACLE 11g: INTERVAL-LIST Composite Partitioning

For the automatic creation of new Partitions in the Intervalpartitioning there are two options:
NUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal. (Use ‚YEAR‘ or ‚MONTH‘)
NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. (Use ‚DAY‘)

EXAMPLE

CREATE TABLE AUDIT_partitioned
(
TICKETID NUMBER(11, 0)
, CHANGETO VARCHAR2(200 BYTE)
, CHANGEDT DATE
, CHANGEUSER VARCHAR2(200 BYTE)
, ACTIONNO NUMBER(6, 0)
, ACTIONTEXT VARCHAR2(4000 BYTE)
, BANKNO VARCHAR2(10 BYTE)
)
PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTODSINTERVAL (1, 'DAY')) -- daily partitions
--PARTITION BY RANGE (CHANGEDT) INTERVAL(NUMTOYMINTERVAL (1, 'MONTH')) -- monthly partitions
SUBPARTITION BY LIST (BANKNO)
SUBPARTITION TEMPLATE
(
SUBPARTITION bank001 VALUES ('001'),
SUBPARTITION bank011 VALUES ('011'),
SUBPARTITION bank014 VALUES ('014'),
SUBPARTITION bankdef VALUES (DEFAULT)
)
( PARTITION p_genaudit_20140101 VALUES LESS THAN (TO_DATE('20140102', 'YYYYMMDD'))
);

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.