Search My Oracle Blog

Custom Search

22 December, 2013

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.

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016