Changing Oracle partitioned table to using interval feature

Oracle 11g has new feature to allows for automatic creation of new partitions. It's easy to create table that for instance has automatic range partitions for every month. But how can you change already partitioned table to using interval partitions?

First of all, you cannot have partition that is the 'last' partition meaning it's high_value is MAX_VALUE. You have to delete that partition somehow.There are some methods for doing that but I'll go for easy one.

The examples I have copied from
Rittman Mead.com:Investigating Oracle 11g interval partitioning
I'll explain later why.

First create normally partitioned table
CREATE TABLE INTERVAL_SALES
      ( prod_id        NUMBER(6)
      , cust_id        NUMBER
      , time_id        DATE
      , channel_id     CHAR(1)
     , promo_id       NUMBER(6)
     , quantity_sold  NUMBER(3)
     , amount_sold    NUMBER(10,2)
     )
   PARTITION BY RANGE (time_id)
      ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2005', 'DD-MM-YYYY')),
        PARTITION p1 VALUES LESS THAN (TO_DATE('01-01-2006', 'DD-MM-YYYY')),
        PARTITION p3 VALUES LESS THAN (TO_DATE('01-01-2007', 'DD-MM-YYYY')),
        PARTITION p4 VALUES LESS THAN (MAXVALUE)
        );

-- add some old data
insert into interval_sales values (1,2,to_date('20041201', 'YYYYMMDD'),'d',3,4,9.9);
insert into interval_sales values (1,2,to_date('20051201', 'YYYYMMDD'),'d',3,4,9.9);
insert into interval_sales values (1,2,to_date('20061201', 'YYYYMMDD'),'d',3,4,9.9);
insert into interval_sales values (1,2,to_date('20070101', 'YYYYMMDD'),'d',3,4,9.9);
commit;

-- Show how rows are distributed in partitions
analyze table INTERVAL_SALES compute statistics;
SELECT
   PARTITION_NAME "NAME",
   PARTITION_POSITION "POSITION",
   NUM_ROWS,
   HIGH_VALUE
FROM
   USER_TAB_PARTITIONS
WHERE
   TABLE_NAME in ('INTERVAL_SALES')
ORDER BY
   PARTITION_POSITION;


NAME     POSITION   NUM_ROWS HIGH_VALUE
------ ---------- ---------- --------------------------------------------------------------------------------
P0              1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1              2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3              3          1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P4              4          1 MAXVALUE



We cannot drop the last partition because it already contains data.

So we split the partition. It's best to split to partition sizes that you are going to use in interval.

alter table interval_sales
split partition P4
at ( to_date( '20070201', 'YYYYMMDD' ) )
into ( partition PART200701, partition PART_LAST ) update global indexes;


If you have already a lot of data, this may take some time....

Analyze table and query partition data:

NAME         POSITION   NUM_ROWS HIGH_VALUE
---------- ---------- ---------- --------------------------------------------------------------------------------
P0                  1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1                  2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                  3          1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200701          4          1 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART_LAST           5          0 MAXVALUE


If you still have data in last partition , split some more.
But like I said, this is not the fastest method. Every split has to read&write all rows in partition.

But the last partition is now empty, so we can drop it.
alter table interval_sales drop partition part_last update global indexes;

Now we can change the table to interval partioning:
alter table interval_sales set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

Now we add some new data :
insert into interval_sales values (1,2,to_date('20071201', 'YYYYMMDD'),'d',3,4,9.9);
commit;


NAME         POSITION   NUM_ROWS HIGH_VALUE
---------- ---------- ---------- --------------------------------------------------------------------------------
P0                  1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1                  2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                  3          1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200701          4          1 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P221            5          1 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


It has made a new partition for December 2007!

Now we add some more data to 2007:
insert into interval_sales values (1,2,to_date('20070801', 'YYYYMMDD'),'d',3,4,9.9);
commit;


NAME         POSITION   NUM_ROWS HIGH_VALUE
---------- ---------- ---------- --------------------------------------------------------------------------------
P0                  1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1                  2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                  3          1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200701          4          1 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P241            5          1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P221            6          1 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


Because the interval is one month, it has created new monthly partition also for new row.

But this was the problem I started thinking when I read Rittman Mead's blog post. What happens when you add new rows to old yearly partitions? The old yearly partitions have data inside that spans over whole year. Does the interval feature also create new monthly partition to old data? If it does, does it also move data from old yearly partition to new monthly partition? Lots of questions so let's try!

insert into interval_sales values (1,2,to_date('20060712', 'YYYYMMDD'),'d',3,4,9.9);
commit;


NAME         POSITION   NUM_ROWS HIGH_VALUE
---------- ---------- ---------- --------------------------------------------------------------------------------
P0                  1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1                  2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                  3          1 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200701          4          1 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P241            5          1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P221            6          1 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


No, it does not create new partition. Ok. How does it know which partition was created manually and which with monthly interval? There's a new column 'interval' for table user_tab_partitions.

SELECT
   PARTITION_NAME "NAME",
   PARTITION_POSITION "POSITION",
   INTERVAL
FROM
   DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME in ('INTERVAL_SALES')
ORDER BY
   TABLE_NAME, PARTITION_POSITION;
 

NAME   POSITION INTERVAL
---------- -------------- --------------
P0                           1 NO
P1                           2 NO
P3                           3 NO
PART200701          4 NO
SYS_P241              5 YES
SYS_P221              6 YES


So when partitioning is set to interval, it only splits new interval partitions, not the old manually created ones. 

Ok, let's get difficult.

insert into interval_sales values (1,2,to_date('20071224', 'YYYYMMDD'),'d',3,4,9.9);
commit;

Now we have to rows at December 2007, one in 1st day and one in 24th.

Now we change the partition interval to days:
alter table interval_sales set INTERVAL(NUMTODSINTERVAL(1, 'day')) ;

And add new row to 15th day:
insert into interval_sales values (1,2,to_date('20071215', 'YYYYMMDD'),'d',3,4,9.9);
commit;



NAME         POSITION   NUM_ROWS HIGH_VALUE
---------- ---------- ---------- --------------------------------------------------------------------------------
P0                  1          1 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1                  2          1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3                  3          2 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PART200701          4          1 TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P241            5          1 TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P221            6          3 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


So it does not split it to daily partitions. Why not?
Because when we ran alter table interval, Oracle changes every partition to manually created:

NAME  POSITION INTERVAL
---------- ------------- ---
P0                         1 NO
P1                         2 NO
P3                         3 NO
PART200701        4 NO
SYS_P241            5 NO
SYS_P221            6 NO



I'm out of tricks, Oracle has thought of everything :)

Comments