范围-具有间隔的范围组合分区-Oracle 11g R2

debugcn 发表于 Dev

Smart_Oracle

我想做的是在同一分区键上创建一个包含Composite Range分区的表-按月分区和按天分区。

我已经尝试使用maxvalue或day interval作为子分区，但是它仍然无法正常工作，而且我没有主意。分区范围是2年，因此代码很长。我有一个分区范围的片段。

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'))
  ),

温弗里德（Wernfried Domscheit）

我认为按月划分分区，按日划分分区是没有用的。只需按月（或按天，具体取决于您的需要）进行分区即可。

无论如何，此SQL应该可以工作：

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')
);