19 | 08 | 2017
Latest Articles
Popular Articles

Database Design

Range partitioned tables

User Rating:  / 0
PoorBest 

Range partitioned tables

 

See also Partition Administration

When we use range partitioning every partition has its partition boundaries defined by a lower limit and an upper limit. However at creation time we only define the upper boundary. The partition key is typically a date or a timestamp. Note in the examples below how we use the attribute VALUES LESS THAN to define the partition boundary. The partition with the highest boundary can have the MAXVALUE attribute or a real future date value.

The advantage of using the MAXVALUE attribute is that is does not require maintenance. If the DBA forget to create a new partition for a new date period, new data gets automatically inserted into the partition with the highest boundary, the one with "values less than MAXVALUE".
The disadvantage is that the "alter table table_name split partition partition_name at" syntax has to be used in order to move data from one partition to another.

The advantage of using a real date upper boundary is that it does not require the "alter table table_name split partition partition_name at" syntax. Instead the "alter table table_name add partition partition_name values less than" can be used which causes less overhead and which is more online.
The disadvantage of using a real date upper boundary is that it requires a maintenance operation in time (limited effort). If the DBA forget to create a new partition for a new date period, new data cannot be inserted !!


Advantages of range partitioning are

1. Possible partition pruning, if we query the table with in the where clause the partition key.

2. Possible partition wise joins if we join tables and query the tables with in the where clause the partition key.

3. Possible to store every partition in its dedicated tablespace, which can be put read only after a given time period. Every tablespace can be stored on a separate physical device in order to boost IO performance.


This is an example of a range partitioned heap organized table. The partition key is the insertdate with a default value of sysdate. Note this table contains also a clob column of which the storage parameters are not relevant with respect to range partitioning

create table TABLE_T1  (

   T1_ID                         NUMBER(18)                  not null,
   T1_CREATIONDATE      DATE                            default SYSDATE not null,
   T1_SOMEFIELD            CLOB                            not null
   constraint C_CK_T1_SOMEFIELD check (length(T1_SOMEFIELD) > 0) initially deferred,
   constraint C_PK_T1 primary key (T1_ID)
         using index
       global partition by hash
    ( T1_ID )
     partitions 10
       initrans 2
       tablespace TS_MY_DB_INDEX
)
pctfree 5
initrans 2
  compress
  partition by range
 ( T1_CREATIONDATE)
    (
  partition
             T1_P1207
            values less than ( TO_DATE('01/01/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_1207,
  partition
             T1_P0108
            values less than ( TO_DATE('01/02/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0108,
  partition
             T1_P0208
            values less than ( TO_DATE('01/03/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0208,
  partition
             T1_P0308
            values less than ( TO_DATE('01/04/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0308,
  partition
             T1_P0408
            values less than ( TO_DATE('01/05/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0408,
  partition
             T1_P0508
            values less than ( TO_DATE('01/06/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0508,
  partition
             T1_P0608
            values less than ( TO_DATE('01/07/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0608
    );


xxx_tab_partitions can be queried to explore the partition boundaries

SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='TABLE_T1';


TABLE_NAME                     PARTITION_NAME


HIGH_VALUE



TABLE_T1                T2_P0108
TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T2_P0208
TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T1_P0308
TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T1_P0408
TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T1_P0508
TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T1_P0608
TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')


TABLE_T1                T1_P1207
TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA')



This is an example of a range partitioned index organized table (IOT). It is a requirement that the partition key is part from the IOT primary key.
The partition key is again the inserdate with a default value of sysdate.
Note this table contains also a clob column of which the storage parameters are not relevant with respect to range partitioning

create table TABLE_T2  (

   T2_ID                 NUMBER(18)          constraint NN_T2_T2ID not null,
   SOMEFIELD         CLOB                    constraint NN_T2_SOMEFIELD not null,
   INSERTDATE           DATE                 default SYSDATE not null,
   constraint PK_T2D primary key (T2_ID, INSERTDATE)
)
  organization index tablespace TS_MY_DB_LOB_0208 initrans 2 compress
  lob
 ( SOMEFIELD  )
    store as
         T2_LOB
        (
             disable storage in row
             nocache
             logging
        )
  partition by range
 ( insertdate )
    (
  partition
             T2_P0208
            values less than ( TO_DATE('01/03/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0208
  compress,
  partition
             T2_P0308
            values less than ( TO_DATE('01/04/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0308
  compress,
  partition
             T2_P0408
            values less than ( TO_DATE('01/05/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0408
  compress,
  partition
             T2_P0508
            values less than ( TO_DATE('01/06/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0508
  compress,
  partition
             T2_P0608
            values less than ( TO_DATE('01/07/2008','DD/MM/YYYY') )
  tablespace TS_MY_DB_LOB_0608
  compress
    );

 

Here is an example with MAXVALUE. Note at the bottom end how I use the split partition syntax.

 

 

SQL> create table TABLE_T3 (

  2   T3_ID NUMBER(18) not null,
  3   T3_CREATIONDATE  DATE default SYSDATE not null,
  4   T3_SOMEFIELD varchar2(1000) )
  5  partition by range (T3_CREATIONDATE)
  6   ( partition T3_P0308? values less than ( TO_DATE('01/04/2008','DD/MM/YYYY') ),
  7     partition T3_P0408? values less than (maxvalue) );

Table created.


SQL> set pagesize 9999
SQL> set long 20000
SQL> select table_name,partition_name,high_value from user_tab_partitions where
table_name='TABLE_T3';

TABLE_NAME                     PARTITION_NAME


HIGH_VALUE



TABLE_T3                       T3_P0308
TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TABLE_T3                       T3_P0408
MAXVALUE


SQL> alter table TABLE_T3 split partition T3_P0408? at ( TO_DATE('01/05/2008','DD/MM/YYYY')) into (partition T3_P0408?, partition T3_P0508);

Table altered.

SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='TABLE_T3';

TABLE_NAME                     PARTITION_NAME


HIGH_VALUE



TABLE_T3                       T3_P0308
TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TABLE_T3                       T3_P0408
TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

TABLE_T3                       T3_P0508
MAXVALUE