Partitioning in Oracle

Partitioning in Oracle is a way of distributing table data into separate segments to improve performance of data retrieval. A table with more than 2GB of data volume is generally considered candidate of partitioning. Data is a partitioned table are physically divided and stored into separate physical datafiles, but it is transparent to the end user.

A table can be partitioned in different ways:

--> Range Partition
--> List Partition
--> Hash Partition

Range Partition:

A table can be divided using range partitioning if you want to distribute the data using the range of values of some columns of a table. Generally a data column is considered as a partition key for range partitioning. Like different partitions for Jan-2011, Feb-2011, ......,Dec-2011.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY RANGE(COLUMN2)
(
PARTITION PART1 VALUES LESS THAN (TO_DATE('01/02/2011','DD/MM/YYYY')),
PARTITION PART2 VALUES LESS THAN (TO_DATE('01/03/2011','DD/MM/YYYY')),
....
....
PARTITION PART12 VALUES LESS THAN (TO_DATE('01/12/2011','DD/MM/YYYY')),
);

List Partition:

A table can be divided using list partitioning if you want to distribute the data using the listed values of some columns of a table. This partitioning method is generally used if most of the time the table is queried using the value of the column.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY LIST(COLUMN2)
(
PARTITION PART1 VALUES ('A','B'),
PARTITION PART2 VALUES ('C','D','E'),
....
....
PARTITION PART12 VALUES ('G','H','V'),
);

Hash Partition:

Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning.

Syntax:

CREATE TABLE SAMPLE
(
COLUMN1 VARCHAR2(10),
COLUMN2 DATE,
COLUMN3 NUMBER(10,2)
)TABLESPACE TABLE1
PARTITION BY HASH(COLUMN2)
PARTITIONS n
STORE IN (TS1,TS2,....TSn);


Key points while partitioning a table:

1. Table containig LONG and LONG RAW datatype column cannot be partitioned.
2. Pseudo columns like LEVEL,ROWID,MLSLABEl cannot be used as a partition key.
3. Partition key columns can contain columns that are nullable.
4. In case of range or hash partitioning you can use upto maximum 16 columns a partition key.
5. In case of a partitioned table, you cannot define a unique local index if the partitioning key is not a part of index key.
6. A bitmap index cannot be global index on a partitioned table.