Database Design
Range partitioned tables
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 22:43
-
Last Updated on Thursday, 29 March 2012 17:22
-
Published on Thursday, 18 March 2010 22:43
-
Written by Guy Lambregts
-
Hits: 3862
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