Possible to modify an existing partitioned Oracle table to utilize INTERVAL? [duplicate]

I have a table which is partitioned as below and has millions of rows of data. Table size is 120 GB.

PARTITION BY RANGE (Read_time) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))

Now I want to change to this partitioning strategy with existing data and future data. Table is inserted daily by job.

 PARTITION BY RANGE (Read_time) INTERVAL(NUMTODSINTERVAL(1, 'DAY'))

I have 12 month partition (Oct'18 to Nov'19). I want partition to be converted to daywise.

For example: For Jul'19 partition, it should to be splitted to 01 Jul'19, 02 Jul'19 ....... 31 Jul'19.

Data should also be moved to new partition

I tried split partition. New partition got created day wise , but rows didn't move to new partition.

Solution 1:

To change for monthly interval to daily is a simple as

 ALTER TABLE test SET INTERVAL (NUMTODSINTERVAL(1,'DAY'));

see the documentation for further details

Lets illustrate in on a small example

create table test 
(dt date)
PARTITION BY RANGE (dt)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-01-2019','DD-MM-YYYY'))
);

insert into test (dt)
select date'2019-01-01' + rownum dt from dual 
connect by level <= 100;

The table in montly intrval partitioned and contains 100 days of data - yielding in one initial partition and 4 new montly partition.

PARTITION_NAME                 HIGH_VALUE                                                                      
------------------------------ --------------------------------------------------------------------------------
PART_01                        TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93334                     TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93335                     TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93336                     TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93337                     TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Now we switch to daily partitioning

 ALTER TABLE test SET INTERVAL (NUMTODSINTERVAL(1,'DAY'));

This makes nothing with existing data (so it is instant operation), also the new data for the current month will go in the current (month) partition.

The data loaded for the new month will be partitioned on daily basis.

Lets insert few new days

insert into test (dt)
select date'2019-01-01' + 100 +rownum dt from dual 
connect by level <= 22;


PARTITION_NAME                 HIGH_VALUE                                                                      
------------------------------ --------------------------------------------------------------------------------
PART_01                        TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93334                     TO_DATE(' 2019-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93335                     TO_DATE(' 2019-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93336                     TO_DATE(' 2019-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93337                     TO_DATE(' 2019-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93338                     TO_DATE(' 2019-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93339                     TO_DATE(' 2019-05-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P93340                     TO_DATE(' 2019-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

As expected starting with May the data are daily partitioned.

If you need also to re-partitioned your historical data, this will require reorganisation (data movement - will take some time for 120GB) using split partition.

alter table test
    SPLIT PARTITION FOR(TO_DATE('2019-02-01','yyyy-mm-dd'))
    AT (TO_DATE('2019-02-27','yyyy-mm-dd'));
alter table test
    SPLIT PARTITION FOR(TO_DATE('2019-02-01','yyyy-mm-dd'))
    AT (TO_DATE('2019-02-26','yyyy-mm-dd'));

This splits the last day in a new partition, you'll have to do it for each day in each monthly partition.

After this step also the history data is partitioned per day:

select * from test partition for ( DATE'2019-02-26');  

DT                 
-------------------
26.02.2019 00:00:00