Oracle Partitioning Types - V (Reference Partition)

Reference Partitioning:

As the name suggests, this has got to do with parent child relations between tables or rather primary key and foreign key relations between two tables. The table is partitioned by reference when the partition key value in a foreign key constraint of that table. The table with the primary key should be partition table, single level or composite partitioned. Fo example we will first use a single level partition table.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

Now we will create a child table with for our DATA_LOAD table.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6),
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
) PARTITION BY REFERENCE(data_load_fk)
*
ERROR at line 8:
ORA-14652: reference partitioning foreign key is not supported

SQL> !oerr ora 14652
14652, 00000, "reference partitioning foreign key is not supported"
// *Cause: The specified partitioning foreign key was not supported
// for reference-partitioned tables. All columns of the
// partitioning foreign key must be constrained NOT NULL with
// enabled, validated, and not deferrable constraints. Furthermore,
// a virtual column cannot be part of the partitioning foreign key.
//* Action: Correct the statement to specify a supported
// partitioning foreign key.

I realsized that the referencing column should be not null as its referencing a primary key column. The error can we misleading.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

Lets check the partitions for this referenced partition table.

SQL> col TABLE_NAME format a15
SQL> col PARTITION_NAME format a15
SQL> col HIGH_VALUE format a80
SQL> set lines 300
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_02_2009 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_03_2009 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_04_2009 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,HIGH_VALUE from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';

TABLE_NAME COM PARTITION_NAME HIGH_VALUE
--------------- --- --------------- --------------------------------------------------------------------------------
DATA_LOAD_FK NO DATA_01_2009
DATA_LOAD_FK NO DATA_02_2009
DATA_LOAD_FK NO DATA_03_2009
DATA_LOAD_FK NO DATA_04_2009

So we can see that four partitions were created with the same name as the primary table. And no high value is available as the data will be stored on the basis on the data stored in the primary table.
Lets test that also, by adding a row.

SQL> insert into DATA_LOAD values (1,1,sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> insert into DATA_LOAD_FK values (1,'TEST',sysdate-90,1,'DELHI');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> analyze table DATA_LOAD_FK compute statistics;
Table analyzed.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME NUM_ROWS
--------------- --- --------------- ----------
DATA_LOAD_FK NO DATA_01_2009 0
DATA_LOAD_FK NO DATA_02_2009 1
DATA_LOAD_FK NO DATA_03_2009 0
DATA_LOAD_FK NO DATA_04_2009 0

So we can see that the partition in which the row goes in the reference partitioned table is the same as the primary table.

Now let us see the affect of composite partition table as the primary table.

SQL> drop table DATA_LOAD_FK;
Table dropped.

SQL> drop table DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_pk PRIMARY KEY(DATA_id)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> CREATE TABLE DATA_LOAD_FK
( DATA_id NUMBER(6) NOT NULL,
source_name VARCHAR2(20),
timestamp DATE,
data_size NUMBER,
state VARCHAR2(20),
CONSTRAINT data_load_fk FOREIGN KEY(DATA_id) REFERENCES DATA_LOAD(DATA_id)
) PARTITION BY REFERENCE(data_load_fk);
Table created.

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD YES DATA_01_2009 3
DATA_LOAD YES DATA_02_2009 3
DATA_LOAD YES DATA_03_2009 3
DATA_LOAD YES DATA_04_2009 3

SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD_FK';
TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_03_2009_2 0
DATA_LOAD_FK NO DATA_03_2009_3 0
DATA_LOAD_FK NO DATA_04_2009_1 0
DATA_LOAD_FK NO DATA_04_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_1 0
DATA_LOAD_FK NO DATA_01_2009_2 0
DATA_LOAD_FK NO DATA_01_2009_3 0
DATA_LOAD_FK NO DATA_02_2009_1 0
DATA_LOAD_FK NO DATA_02_2009_2 0
DATA_LOAD_FK NO DATA_02_2009_3 0
DATA_LOAD_FK NO DATA_03_2009_1 0

TABLE_NAME COM PARTITION_NAME SUBPARTITION_COUNT
--------------- --- --------------- ------------------
DATA_LOAD_FK NO DATA_04_2009_3 0
12 rows selected.

So you can see that the subpartitions have become individual partitions in the reference partition table.

Partitions limits cannot be specified in a reference partition table.

The partitions take the same name as the parent table partitions.

Part I
Part II
Part III
Part IV
Part VI
Part VII

Oracle Partitioning Types - III (Composite Partitions)

Composite Partitions:
1.) Composite Range-Range Partitioning
(Available in 11g. Oracle 10g would give error: ORA-14151)

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20)
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (target_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01
(SUBPARTITION data_01_2009_1 VALUES LESS THAN (TO_DATE('15-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (TO_DATE('25-JAN-2009','DD-MON-YYYY')),
SUBPARTITION data_01_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02
(SUBPARTITION data_02_2009_1 VALUES LESS THAN (TO_DATE('15-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (TO_DATE('25-FEB-2009','DD-MON-YYYY')),
SUBPARTITION data_02_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03
(SUBPARTITION data_03_2009_1 VALUES LESS THAN (TO_DATE('15-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (TO_DATE('25-MAR-2009','DD-MON-YYYY')),
SUBPARTITION data_03_2009_3 VALUES LESS THAN (MAXVALUE)
) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
(SUBPARTITION data_04_2009_1 VALUES LESS THAN (TO_DATE('15-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (TO_DATE('25-APR-2009','DD-MON-YYYY')),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.


2.) Composite Range-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY HASH (source_id)
SUBPARTITIONS 4 STORE IN (DATA_01, DATA_02, DATA_03, DATA_04)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.


3.) Composite Range-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE (
SUBPARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
SUBPARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
SUBPARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
SUBPARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
SUBPARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
SUBPARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) ,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) ,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) ,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY'))
);
Table created.


4.) Composite List-Range Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY RANGE (load_date)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
( SUBPARTITION n_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION n_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION n_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION n_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
( SUBPARTITION w_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION w_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION w_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION w_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
( SUBPARTITION s_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION s_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION s_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION s_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
( SUBPARTITION e_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION e_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION e_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION e_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION NO_REGION VALUES (NULL)
( SUBPARTITION no_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION no_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION no_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION no_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
),
PARTITION OTHER VALUES (DEFAULT)
( SUBPARTITION o_data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
SUBPARTITION o_data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
SUBPARTITION o_data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
SUBPARTITION o_data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
)
);
Table created.


5.) Composite List-Hash Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY HASH (load_date) SUBPARTITIONS 4
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.


6.) Composite List-List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
status VARCHAR2(1),
state VARCHAR2(20))
PARTITION BY LIST (state)
SUBPARTITION BY LIST (status)
(
PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA')
(SUBPARTITION a_NORTH_01 VALUES ('A'),
SUBPARTITION b_NORTH_01 VALUES ('B'),
SUBPARTITION c_NORTH_01 VALUES ('C')
),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA')
(SUBPARTITION a_WEST_01 VALUES ('A'),
SUBPARTITION b_WEST_01 VALUES ('B'),
SUBPARTITION c_WEST_01 VALUES ('C')
),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA')
(SUBPARTITION a_SOUTH_01 VALUES ('A'),
SUBPARTITION b_SOUTH_01 VALUES ('B'),
SUBPARTITION c_SOUTH_01 VALUES ('C')
),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND')
(SUBPARTITION a_EAST_01 VALUES ('A'),
SUBPARTITION b_EAST_01 VALUES ('B'),
SUBPARTITION c_EAST_01 VALUES ('C')
),
PARTITION NO_REGION VALUES (NULL)
(SUBPARTITION a_NO_REGION_01 VALUES ('A'),
SUBPARTITION b_NO_REGION_01 VALUES ('B'),
SUBPARTITION c_NO_REGION_01 VALUES ('C')
),
PARTITION OTHER VALUES (DEFAULT)
(SUBPARTITION a_OTHER_01 VALUES ('A'),
SUBPARTITION b_OTHER_01 VALUES ('B'),
SUBPARTITION c_OTHER_01 VALUES ('C')
)
);
Table created.

Part I
Part II
Part IV
Part V
Part VI
Part VII

Oracle Partitioning Types - II (Single Level Partitions)

Syntax examples on how to create partition tables:
1.) Range Partitioning:

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (load_date)
( PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-FEB-2009','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (TO_DATE('01-MAR-2009','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (TO_DATE('01-APR-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (TO_DATE('01-MAY-2009','DD-MON-YYYY')) TABLESPACE DATA_04
);

Table created.

This create Date range based partition each for the month of JAN, FEB, MAR & APR 2009.

2.) Hash Partitioning

Oracle apply's a hashing algorithm to the column selected on partitions, we can provide the number of partitions we want or also the names of partitions.

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id)
( PARTITION data_01_2009 TABLESPACE DATA_01,
PARTITION data_02_2009 TABLESPACE DATA_02,
PARTITION data_03_2009 TABLESPACE DATA_03,
PARTITION data_04_2009 TABLESPACE DATA_04
);
Table created.

Alternatively,
SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY HASH (source_id) PARTITIONS 4;
Table created.

3.) List Partitioning

SQL> DROP TABLE DATA_LOAD;
Table dropped.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY LIST (state)
(PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA'),
PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA'),
PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA'),
PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND'),
PARTITION NO_REGION VALUES (NULL),
PARTITION OTHER VALUES (DEFAULT)
);
Table created.

Part I
Part III
Part IV
Part V
Part VI
Part VII

Oracle Partitioning Types - I (Index)

Types of Oracle partitions:

Single Level Partitions:

Range Partitioning

Hash Partitioning

List Partitioning


Composite Partitions:

Composite Range-Range Partitioning

Composite Range-Hash Partitioning

Composite Range-List Partitioning

Composite List-Range Partitioning

Composite List-Hash Partitioning

Composite List-List Partitioning

Enhancements in 11g:

Interval Partitioning

Reference Partitioning

Multiple Column Partitioning

Virtual column-based Partitioning

Partition Advisor

Reasons for Partitioning od Data:
1.) Large amount of data in a table, oracle suggests that table size of over 2G are good candidates for partitioning.
2.) Historical data and current data reside in the table at the same time.
3.) When the disk space is limited and you want to span a table across different storage types.
4.) Partition is good for inproving performance, as the data related to different regions, dates etc can be stored seperately and can be queries faster. For example, data for last 2-3 yrs reside in the same table, older data is moreover read-only and the current data is queries more than the other, always good to have partitions like monthly etc. another example would be data of different states of a country reside in the same table, they can be seperated stored state-wise.

For details on the syntax of these partitioning types:
Single Level Partitions, Part II
Composite Partitions, Part III
Interval Partitions, Part IV
Reference Partitions, Part V
Multicolumn Partitioning, Part VI
Virtual Column Based Partitioning, Part VII