Split Range Partition

Splitting Range Partitions

CREATE TABLE range_sales

    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (

   PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01/03/2020','DD/mm/YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01/06/2020','DD/mm/YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01/09/2020','DD/mm/YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));

ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO
   (
PARTITION sales_jan_2012 values less than (TO_DATE('01/01/2020','DD/mm/YYYY')),
PARTITION sales_feb_2012 values less than (TO_DATE('01/02/2020','DD/mm/YYYY')),
PARTITION SALES_Q1_2012);

SALES_JAN_2012 TO_DATE(' 2020-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SALES_FEB_2012 TO_DATE(' 2020-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SALES_Q1_2012 TO_DATE(' 2020-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SALES_Q2_2012 TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SALES_Q3_2012 TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SALES_Q4_2012 MAXVALUE

Yukarıdaki örnekte  sales_q1_2012 partition'ını daha küçük ay aralıklarına böldük.

Aşağıda da benzer şekilde subpartitionlar oluşturulmuştur.

ALTER TABLE range_sales SPLIT SUBPARTITION sales_q1_2012 INTO
(SUBPARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
SUBPARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
SUBPARTITION sales_mar_2012);