A quick demo of INTERVAL Partitioning.
(This demo in 11.2.0.2)
The automatically created partition for the February-2013 data has been given a "system-generated" name. Let's proceed with a few more rows :
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 !
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.
Now that we have two different interval-partitioned tables with multiple partitions, let's query for their partition names.
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.
.
.
.
(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.
.
.
.