CODEWITHSUNDEEP
oracle-databaseoracle11gpartitioningdatabase-partitioningoracle11gr2
Smart_Oracle
Smart_Oracle

Reputation: 3

Range - Range Composite Partitioning with Interval - Oracle 11g R2

What I'm trying to do is create a table with Composite Range partitioning - Partition by Month and Subpartition by Day on the same partition key.

I've tried using either maxvalue or day interval for the subpartition but it still doesn't work and I'm out of ideas. The partition range is 2 years so the codes are long. I've a snippet of the partitioning range.

PARTITION BY RANGE (SCL_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
  SUBPARTITION BY RANGE(SCL_DATE)
  (
      PARTITION JAN14 VALUES LESS THAN (TO_DATE('01/02/2014', 'DD/MM/YYYY'))
      (
        SUBPARTITION JAN14_1 VALUES LESS THAN(TO_DATE('01/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_2 VALUES LESS THAN(TO_DATE('02/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_3 VALUES LESS THAN(TO_DATE('03/01/2014', 'DD/MM/YYYY')),
        PARTITION JAN14_4 VALUES LESS THAN(TO_DATE('04/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_5 VALUES LESS THAN(TO_DATE('05/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_6 VALUES LESS THAN(TO_DATE('06/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_7 VALUES LESS THAN(TO_DATE('07/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_8 VALUES LESS THAN(TO_DATE('08/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_9 VALUES LESS THAN(TO_DATE('09/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_10 VALUES LESS THAN(TO_DATE('10/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_11 VALUES LESS THAN(TO_DATE('11/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_12 VALUES LESS THAN(TO_DATE('12/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_13 VALUES LESS THAN(TO_DATE('13/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_14 VALUES LESS THAN(TO_DATE('14/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_15 VALUES LESS THAN(TO_DATE('15/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_16 VALUES LESS THAN(TO_DATE('16/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_17 VALUES LESS THAN(TO_DATE('17/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_18 VALUES LESS THAN(TO_DATE('18/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_19 VALUES LESS THAN(TO_DATE('19/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_20 VALUES LESS THAN(TO_DATE('20/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_21 VALUES LESS THAN(TO_DATE('21/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_22 VALUES LESS THAN(TO_DATE('22/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_23 VALUES LESS THAN(TO_DATE('23/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_24 VALUES LESS THAN(TO_DATE('24/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_25 VALUES LESS THAN(TO_DATE('25/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_26 VALUES LESS THAN(TO_DATE('26/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_27 VALUES LESS THAN(TO_DATE('27/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_28 VALUES LESS THAN(TO_DATE('28/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_29 VALUES LESS THAN(TO_DATE('29/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_30 VALUES LESS THAN(TO_DATE('30/01/2014', 'DD/MM/YYYY')),
        SUBPARTITION JAN14_31 VALUES LESS THAN(TO_DATE('31/01/2014', 'DD/MM/YYYY'))
  ),

Upvotes: 0

Views: 1548

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

I don't think it is useful to make partitions by Month and sub-partitions by Day. Simply make partitions by Month (or by Day, depending on your need).

Anyway, this SQL should work:

CREATE TABLE THE_TABLE
(
  SCL_DATE  TIMESTAMP(0),
  SCL_DAY   NUMBER GENERATED ALWAYS AS (EXTRACT(DAY FROM SCL_DATE)) VIRTUAL,
  ... more COLUMNS
)
PARTITION BY RANGE (SCL_DATE) INTERVAL (INTERVAL '1' MONTH)
   SUBPARTITION BY LIST ( SCL_DAY )
   SUBPARTITION TEMPLATE
     (SUBPARTITION P01 VALUES (1),
      SUBPARTITION P02 VALUES (2),
      SUBPARTITION P03 VALUES (3),
      ...
      SUBPARTITION P30 VALUES (30)
      SUBPARTITION P31 VALUES (31)
      )
(  
  PARTITION P_201401 VALUES LESS THAN (TIMESTAMP '2014-01-01 00:00:00')
);

Upvotes: 1

Related Questions