Can i change range interval partition from month to day in oracle
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.
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