INTERVAL partitioning has been introduced by Oracle as an extension of RANGE partitioning. There are few limitations like the fact that’s not supported at subpartition level and the partitioning key has to be a DATE or NUMBER but also some interesting advantages. When a table is partitioned by INTERVAL, in fact, partitions are created automatically as data is loaded into the table so we don’t have to bother creating anything in advance. We might, anyway, prefer to maintain our historical data in tables that are partitioned by RANGE. This because it’s common to keep inside the system a “rolling window” (e.g. last 90 days) of historical data meaning oldest partitions are dropped as soon as they become obsolete and with INTERVAL partitioning the last partition in the range section cannot be dropped. Moreover, in many processes it’s preferrable to have a full control of which partitions are created because data loaded into the system is not guaranteed to be “clean” and rows that don’t map to existing partitions have to be rejected.
After all these considerations, let’s have a look at what we can do to make the best out of both partitioning options (tested on 11g).
Let’s create a test table partitioned by RANGE and a couple of indexes
|
1 2 3 4 5 6 7 |
create table mytab(run_date date, id number) partition by range(run_date) (partition P20170101 values less than (to_date('2017-01-02', 'YYYY-MM-DD'))); create index idx1 on mytab(run_date) local; create index idx2 on mytab(id) local; |
The partition created with the table holds data for 1st Jan 2017 so we can insert the following row
|
1 2 3 4 5 |
SQL> insert into mytab values(date '2017-01-01', 1); 1 row inserted SQL> commit; Commit complete |
Obviously, being this table partitioned by RANGE, we can’t insert data for partitions which are not in place yet
|
1 2 3 |
SQL> insert into mytab values(date '2017-01-02', 2); insert into mytab values(date '2017-01-02', 2) ORA-14400: inserted partition key does not map to any partition |
Imagine now that we have a big amount of rows we want to load, data that has been cleansed so we are sure it contains only “RUN_DATE” values that we expect. How to make it fast without manually creating all needed partitions? The best option is to get advantage of INTERVAL partitioning…but wait, isn’t our table partitioned by RANGE? Yes, it is, but we can temporarily change that!
|
1 2 |
SQL> alter table mytab set interval(NUMTODSINTERVAL(1, 'DAY')); Table altered |
and now let’s insert our new rows without caring too much about the existing partitions
|
1 2 3 4 5 6 7 8 |
SQL> insert into mytab values(date '2017-01-02', 2); 1 row inserted SQL> insert into mytab values(date '2017-01-03', 3); 1 row inserted SQL> commit; Commit complete |
As we can see the rows have been inserted and Oracle took care of creating the proper partitions for them
|
1 2 3 4 5 6 7 8 |
SQL> select table_name, partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MYTAB P20170101 TO_DATE(' 2017-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB SYS_P77 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB SYS_P78 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA |
Same happened to all the local indexes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select index_name, partition_name, high_value 2 from user_ind_partitions 3 where index_name in (select index_name from user_indexes where table_name = 'MYTAB') 4 order by 1,2; INDEX_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- IDX1 P20170101 TO_DATE(' 2017-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX1 SYS_P77 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX1 SYS_P78 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 P20170101 TO_DATE(' 2017-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 SYS_P77 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 SYS_P78 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6 rows selected |
Partition names have been generated by Oracle. Index partitions have the same name as the table partitions only if the indexes are on place when the new partition is created. If we add a new local index now, the names of index partitions for this one will be different.
With a small trick, by using “lock table partition”, we can actually manually create a new partition without even inserting data even if the partitioning is by INTERVAL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> lock table mytab partition for (date '2017-01-04') in share mode; Table(s) locked SQL> commit; Commit complete SQL> select table_name, partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MYTAB P20170101 TO_DATE(' 2017-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB SYS_P77 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB SYS_P78 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB SYS_P79 TO_DATE(' 2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA |
Before going on I want to show one very interesting and important thing. By querying the dictionary we can see that there is a flag showing which partitions have been created as interval
|
1 2 3 4 5 6 7 8 9 |
SQL> select table_name, partition_name, interval 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME INTERVAL ------------------------------ ------------------------------ -------- MYTAB P20170101 NO MYTAB SYS_P89 YES MYTAB SYS_P90 YES MYTAB SYS_P91 YES |
That’s correct because the table has been initially created with RANGE partitioning and one partition P20170101, Oracle knows that. This information is lost once we convert the table back to RANGE partitioning by setting the interval to null
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> alter table mytab set interval(); Table altered SQL> select table_name, partition_name, interval 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME INTERVAL ------------------------------ ------------------------------ -------- MYTAB P20170101 NO MYTAB SYS_P89 NO MYTAB SYS_P90 NO MYTAB SYS_P91 NO |
Interestingly, if we convert again to INTERVAL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> alter table mytab set interval(NUMTODSINTERVAL(1, 'DAY')); Table altered SQL> SQL> select table_name, partition_name, interval 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME INTERVAL ------------------------------ ------------------------------ -------- MYTAB P20170101 NO MYTAB SYS_P89 NO MYTAB SYS_P90 NO MYTAB SYS_P91 NO |
we can drop the oldest partition without any problem
|
1 2 |
SQL> alter table mytab drop partition P20170101; Table altered |
This is a table partitioned by interval and we just dropped the last partition in the range section! Actually in this case we can drop any partition and this is possible because none of them is flagged as INTERVAL=YES. As soon as you create at least one new partition with INTERVAL=YES (meaning that Oracle creates it once you insert data) you will have again some restrictions on dropping partitions. To be more specific, you will be able to drop all old partitions till the one right before the newly created with INTERVAL=YES. In our example, after creating a new interval partition, you won’t be able to drop SYS_P91.
Let’s go back to our test and change partitioning to RANGE again
|
1 2 |
SQL> alter table mytab set interval(); Table altered |
Now we have partitions and data but names of partitions have been generated by Oracle
|
1 2 3 4 5 6 7 8 |
SQL> select table_name, partition_name, interval 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME INTERVAL ------------------------------ ------------------------------ -------- MYTAB SYS_P89 NO MYTAB SYS_P90 NO MYTAB SYS_P91 NO |
Even if the partition names are not self-explanatory regarding the data they hold, we can always access the right partition by using the “partition for” syntax
|
1 2 3 4 |
SQL> select * from mytab partition for (date '2017-01-03'); RUN_DATE ID ----------- ---------- 03/01/2017 3 |
Anyway, if we want to use a standard naming format for our partitions (in this example PYYYYMMDD) we can rename them with a simple “alter table mytab rename partition…”.
Here is a script that does it automatically and sets the correct partition name according to the HIGH_VALUE. This is done for index partitions as well
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
declare v_table_name varchar2(30) := 'MYTAB'; v_hv_date date; function str_to_date(p_str in varchar2) return date is v_date date; v_sql varchar2(4000); begin select 'select cast(' || p_str || ' as date) from dual' into v_sql from dual; execute immediate v_sql into v_date; return v_date; end str_to_date; begin -- Rename table partitions for cur_parts in ( with par_xml as ( select dbms_xmlgen.getXMLType('select table_name, partition_name, high_value from user_tab_partitions where table_name = '''||v_table_name||''' and not(regexp_like(partition_name, ''^P[0-9]{8}$''))' ) as col_xml from dual ), partitions as (select x.* from par_xml p, xmltable('/ROWSET/ROW' passing p.col_xml columns table_name varchar2(30) path '/ROW/TABLE_NAME', partition_name varchar2(30) path '/ROW/PARTITION_NAME', high_value varchar2(500) path '/ROW/HIGH_VALUE') x ) select table_name, partition_name, high_value from partitions) loop v_hv_date := str_to_date(cur_parts.high_value) - 1; execute immediate 'alter table '||v_table_name||' rename partition '||cur_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD'); end loop; -- Rename index partitions for cur_idx_parts in ( with par_xml as ( select dbms_xmlgen.getXMLType('select index_name, partition_name, high_value from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = '''||v_table_name||''') and not(regexp_like(partition_name, ''^P[0-9]{8}$''))' ) as col_xml from dual ), partitions as (select x.* from par_xml p, xmltable('/ROWSET/ROW' passing p.col_xml columns index_name varchar2(30) path '/ROW/INDEX_NAME', partition_name varchar2(30) path '/ROW/PARTITION_NAME', high_value varchar2(500) path '/ROW/HIGH_VALUE') x ) select index_name, partition_name, high_value from partitions) loop v_hv_date := str_to_date(cur_idx_parts.high_value) - 1; execute immediate 'alter index '||cur_idx_parts.index_name||' rename partition '||cur_idx_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD'); end loop; end; / |
After executing it we finally have everything with the proper names
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> select table_name, partition_name, high_value 2 from user_tab_partitions 3 where table_name = 'MYTAB'; TABLE_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- MYTAB P20170102 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB P20170103 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA MYTAB P20170104 TO_DATE(' 2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA SQL> SQL> select index_name, partition_name, high_value 2 from user_ind_partitions 3 where index_name in (select index_name from user_indexes where table_name = 'MYTAB') 4 order by 1,2; INDEX_NAME PARTITION_NAME HIGH_VALUE ------------------------------ ------------------------------ -------------------------------------------------------------------------------- IDX1 P20170102 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX1 P20170103 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX1 P20170104 TO_DATE(' 2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 P20170102 TO_DATE(' 2017-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 P20170103 TO_DATE(' 2017-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA IDX2 P20170104 TO_DATE(' 2017-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 6 rows selected |
Hope you enjoyed this journey among RANGE-INTERVAL partitioning!
