INTERVAL Partitioning

A quick demo of INTERVAL Partitioning.

(This demo in 11.2.0.2)

SQL> create table hkc_test_intvl_date
  2  (date_column date, 
  3  data_column varchar2(50))
  4  partition by range (date_column)
  5  interval (numtoyminterval(1,'MONTH'))
  6  (partition p_1 values less than (to_date('01-FEB-2013','DD-MON-YYYY')))
  7  /

Table created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JAN-2013','DD-MON-YYYY'),'First Row')
  3  /

1 row created.

SQL> col high_value format a40         
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 
SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-FEB-2013','DD-MON-YYYY'),'Second Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 

The automatically created partition for the February-2013 data has been given a "system-generated" name. Let's proceed with a few more rows :

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-DEC-2013','DD-MON-YYYY'),'Third Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA


SQL> 

Interesting ! The newly created partition (SYS_P62) for the December-2013 data has a contiguous name but without any intervening partitions.  Oracle names the partitions in the order they are created.  What if we had multiple tables defined with Interval Partioning ?  What names would be assigned ?.
Partitions for intermediate date ranges with no data are NOT created !

SQL> insert into hkc_test_intvl_date
  2  values (to_date('15-JUL-2013','DD-MON-YYYY'),'Fourth Row')
  3  /

1 row created.

SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'HKC_TEST_INTVL_DATE'
  4  order by partition_position
  5  /

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------
P_1                            TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P61                        TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P63                        TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SYS_P62                        TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL>

The new intervening partition for the July-2013 data has been given the name SYS_P63 because it was created *after* the SYS_P62 partition for the December-2013 data.

Let's create another table and a few more partitions.

SQL> create table hkc_test_intvl_number
  2  (id_column number,
  3  data_column varchar2(50))
  4  partition by range (id_column)
  5  interval (100)
  6  (partition P_1 values less than (101))
  7  /

Table created.

SQL> insert into hkc_test_intvl_number
  2  values (51,'Fifty One')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_number
  2  values (253,'Two Hundred Fifty Three')
  3  /

1 row created.

SQL> insert into hkc_test_intvl_date
  2  values (to_date('16-APR-2013','DD-MON-YYYY'),'Fifth Row')
  3  /

1 row created.

SQL> 

Now that we have two different interval-partitioned tables with multiple partitions, let's query for their partition names.

SQL> l
  1  select table_name, partition_name, high_value
  2  from user_tab_partitions
  3  where table_name like 'HKC_TEST_INTVL%'
  4* order by table_name, partition_position
SQL> /

TABLE_NAME            PARTITION_NAME        HIGH_VALUE
--------------------- --------------------- ----------------------------------------
HKC_TEST_INTVL_DATE   P_1                   TO_DATE(' 2013-02-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P61               TO_DATE(' 2013-03-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P65               TO_DATE(' 2013-05-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P63               TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_DATE   SYS_P62               TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M
                                            M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

HKC_TEST_INTVL_NUMBER P_1                   101
HKC_TEST_INTVL_NUMBER SYS_P64               301

7 rows selected.

SQL> 

Notice how SYS_P64 for the new table HKC_TEST_INTVL_NUMBER was created before SYS_P65. So, partition names are not tied to the tables -- they are derived from a sequence and assigned as and when new partitions need to be created.

.
.
.