+---------------------------------- |
| Partitioning in 10g |
| |
+-------------------------------------------------------------------------------------------+
| Techonology : Oracle database 10.2.0 |
+-------------------------------------------------------------------------------------------+
| Created : 28/7/2007 |
+-------------------------------------------------------------------------------------------+
Types of Partitioning
Range
Hash
Composite
List
Range
=====
Based on range of the partition key values.
It is used most commonly for dates.
Range partitioning is defined by the PARTITION BY RANGE(partition_key_column), and for
each sub partition in VALUES LESS THAN(value_list).The literal MAXVALUE represent a
virtual infinite value that sorts higher that any other value for the data type
including the null value.
Range
=====
( Partitioning historical data )
The example below shows how to create a table with range partitioning
CREATE TABLE PROFILE_HISTORY
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK PRIMARY KEY (PROFILE_ID,ACCESS_DATE)
)
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);
The example below shows how to create a INDEX with range partitioning on a normal
table
CREATE TABLE PROFILE_HISTORY2
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK2 PRIMARY KEY (PROFILE_ID)
);
CREATE INDEX PROFILE_HISTORY2_INDX
ON PROFILE_HISTORY2(ACCESS_DATE)
GLOBAL
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN (MAXVALUE)
);
The example below shows how to create a INDEX with range partitioning on a partition
table
CREATE INDEX PROFILE_HISTORY_INDX
ON PROFILE_HISTORY(ACCESS_DATE)
GLOBAL
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN (MAXVALUE)
);
The Local partition index
=========================
The Local partitiom index is created by specified the literal LOCAL.
The local index is created based on partition table.
CREATE TABLE PROFILE_HISTORY3
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CONSTRAINT PROFILE_HISTORY_PK3 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY RANGE(ACCESS_DATE)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);
create index PROFILE_HISTORY3_indx on PROFILE_HISTORY3(PROFILE_ID,ACCESS_DATE)
LOCAL;
SQL> select partition_name
from dba_ind_partitions
where index_name='PROFILE_HISTORY3_INDX';
PARTITION_NAME
------------------------------
P1
P2
P3
P4
P5
CREATE TABLE PROFILE_HISTORY4
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK4 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY RANGE(ACCESS_DATE,CUSTOMER_ID)
(
PARTITION P1 VALUES LESS THAN ( TO_DATE('01-01-2007','DD-MM-YYYY')AND CUSTOMER_ID 10),
PARTITION P2 VALUES LESS THAN ( TO_DATE('01-03-2007','DD-MM-YYYY')),
PARTITION P3 VALUES LESS THAN ( TO_DATE('01-06-2007','DD-MM-YYYY')),
PARTITION P4 VALUES LESS THAN ( TO_DATE('01-09-2007','DD-MM-YYYY')),
PARTITION P5 VALUES LESS THAN ( TO_DATE('01-12-2007','DD-MM-YYYY'))
);
CREATE TABLE PROFILE_HISTORY6
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK6 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID);
CREATE TABLE PROFILE_HISTORY7
(
PROFILE_ID NUMBER,
ACCESS_DATE DATE,
CUSTOMER_ID NUMBER,
CONSTRAINT PROFILE_HISTORY_PK7 PRIMARY KEY (PROFILE_ID)
)
PARTITION BY HASH(PROFILE_ID,CUSTOMER_ID) PARTITION 4;
