Oracle Partitioning VIII - Partition Indexes

Indexing on Partitoned Tables:
Local Indexes:
A local index on a partition table will have the same partition schema as the table. Each partition in a local index will have similar entries like the corresponding data in the table partition.
eg.:
Creating a table first:
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)
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_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) ,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) ,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
Table created.

SQL> create index DATA_LOAD_LOCAL1 on DATA_LOAD (load_date) LOCAL;
Index created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,10,'DELHI');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,20,'RAJASTHAN');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,30,'KERELA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,40,'ASSAM');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,50,'NAGALAND');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,60,'PUNJAB');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,70,'HARYANA');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> analyze index DATA_LOAD_LOCAL1 compute statistics ;
Index analyzed.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_LOCAL1';

INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_LOCAL1 YES DATA_2006 6 5
DATA_LOAD_LOCAL1 YES DATA_2007 6 0
DATA_LOAD_LOCAL1 YES DATA_2008 6 1
DATA_LOAD_LOCAL1 YES DATA_2009 6 1

SO we can see the index partitions were created the same way as the table partition, same subpartitions also.

Prefixed LOCAL Index:
A prefixed local index is an index in which the left most column used in the index definition is the same as the table partitioning key. The above example is a local prefixed index.

LOCAL NON-Prefixed Index:
As the name suggests its opposite to the prefixed local index. In this index the left most key is not the partition key column.

SQL> create index DATA_LOAD_LOCAL2 on DATA_LOAD (source_id) LOCAL;
Index created.

SQL> analyze index DATA_LOAD_LOCAL2 compute statistics;
Index analyzed.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_LOCAL2';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_LOCAL2 YES DATA_2006 6 5
DATA_LOAD_LOCAL2 YES DATA_2007 6 0
DATA_LOAD_LOCAL2 YES DATA_2008 6 1
DATA_LOAD_LOCAL2 YES DATA_2009 6 1

We can see that even in the NON-Prefixed index has the same partitioning scheme as the partition table.
Each partition of the NON-Prefixed Local index will contain the whole range of possible partition key values, so this might we inefficient at time if we are not using parallel option while selecting. As here the partitions for the index are created like the that of the table using the partition key, but the index key is something else.

GLOBAL INDEXES:
Basically Global Indexes are indexes on which we can define the parition scheme, we define how many partitions we want of the index using which ever table column we would like to use. A global index can be created with the table partition key as the leftmost column or with any other column and in the create statement we can define the partition values for the key.

SQL> CREATE INDEX DATA_LOAD_GLOBAL ON DATA_LOAD (load_date,data_size)
GLOBAL PARTITION BY RANGE (load_date)
(PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION p2 VALUES LESS THAN (MAXVALUE)
);
Index created.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_GLOBAL';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_GLOBAL NO P1 0 5
DATA_LOAD_GLOBAL NO P2 0 2

We can see that the partition definition of the index is different from the table.

Actually the Global indexes are more useful when using other column for indexing the table rather than the partition key column of the table. As in our case the partitions are done on the basic on load_date, what if we use the column data_size more often to query than the load_date. In this case if we create a local index on data_size (non-prefixed local index) than its partition defination doesn't help the query as it is partitions are based on the table partitioning.
In our case:
Every partition based on load_date can have all the values of data_size. Say the data size column has values from 1 to 100.
Then DATA_2006 can have values 1 to 100, DATA_2007 can have values 1 to 100 and so on. So this does not help quering this index so much.

Instead we can create a global index on this column and define the correct partition scheme for the index using data_size as the partition key for the index.
SQL> CREATE INDEX DATA_LOAD_GLOBAL2 on DATA_LOAD (data_size)
GLOBAL PARTITION BY RANGE (data_size)
(PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
Index created.

SQL> select INDEX_NAME,COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,NUM_ROWS from user_ind_partitions where index_name='DATA_LOAD_GLOBAL2';
INDEX_NAME COM PARTITION_NAME SUBPARTITION_COUNT NUM_ROWS
------------------------------ --- --------------- ------------------ ----------
DATA_LOAD_GLOBAL2 NO P2 0 2
DATA_LOAD_GLOBAL2 NO P3 0 3
DATA_LOAD_GLOBAL2 NO P4 0 0
DATA_LOAD_GLOBAL2 NO P1 0 2

Note: Global indexed should have same left most index key and the index partition key, they cannot be different.
Else we will receive the below error. They are different from the non-prefixed local partition in this context.

SQL> CREATE INDEX DATA_LOAD_GLOBAL2 on DATA_LOAD (data_size)
GLOBAL PARTITION BY RANGE (source_id)
(PARTITION p1 VALUES LESS THAN (30),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN (80),
PARTITION p4 VALUES LESS THAN (MAXVALUE)
); 2 3 4 5 6 7
GLOBAL PARTITION BY RANGE (source_id)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

Similarly we can have hash and list partition Global indexes:

SQL> CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4;
CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4
*
ERROR at line 1:
ORA-01408: such column list already indexed
We received the above error as we already create an index with the above column.

SQL> CREATE INDEX DATA_LOAD_GLOBAL3 on DATA_LOAD (data_size,source_id) GLOBAL PARTITION BY HASH (data_size) PARTITIONS 4;
Index created.

And so on....

Details about Oracle Partition Methods/Types

Oracle Partitioning - Exchange Partition

The need and use of exchange partitions:

I have used exchange partition to archive old data from an existing partition table. As we have a lot of very large partition tables which store data for past years, we exchange older partitions which don't need to be part of the existing table to a new table and then export it and save it on to a tape device as archive data. This way we are able to store the data out of the database and keep it on tapes for any future use. We can import this data whenever we need. We see that exchange partition is widely used in data warehousing environments to keep the tables in a manageable size, achieve adequate performance apart from data archiving. As using exchange partition we can restore this data back into the old table whenever we need it.
We can use exchange partition to convert a non partitioned table into a partitioned table also.
Syntax and example:

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_2006 VALUES LESS THAN (TO_DATE('01-Jan-2007','DD-MON-YYYY')) TABLESPACE DATA_01,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-Jan-2008','DD-MON-YYYY')) TABLESPACE DATA_02,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-Jan-2009','DD-MON-YYYY')) TABLESPACE DATA_03,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-Jan-2010','DD-MON-YYYY')) TABLESPACE DATA_04
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'CA');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'LA');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'FA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'GA');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'BA');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'RA');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'TA');
1 row created.
SQL> commit;
Commit complete.

SQL> set lines 300
SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA
7 rows selected.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------- --------------- ----------
DATA_LOAD DATA_2006 5
DATA_LOAD DATA_2007 0
DATA_LOAD DATA_2008 1
DATA_LOAD DATA_2009 1

Now lets try and move the DATA_2006 to a new table, lets create the table to exchange the partition with:
SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows from user_tab_partitions where TABLE_NAME='DATA_2006';
no rows selected

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 CA
2 2 10-MAY-06 1 LA
3 3 18-AUG-06 1 FA
4 4 22-OCT-05 1 GA
5 5 26-NOV-06 1 BA

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 RA
7 7 14-MAY-09 1 TA

This was an example of single level partition, lets try a table with composite partitions:

SQL> drop table DATA_LOAD;
Table dropped.
SQL> drop table DATA_2006;
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 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_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','DD-MON-YYYY')) ,
PARTITION data_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')) ,
PARTITION data_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY')) ,
PARTITION data_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);
Table created.

SQL> insert into DATA_LOAD values (1,1,sysdate-1200,1,'DELHI');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-1100,1,'RAJASTHAN');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-1000,1,'KERELA');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-1300,1,'ASSAM');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-900,1,'NAGALAND');
1 row created.
SQL> insert into DATA_LOAD values (6,6,sysdate-300,1,'PUNJAB');
1 row created.
SQL> insert into DATA_LOAD values (7,7,sysdate,1,'HARYANA');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,num_rows from user_tab_subpartitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_2006 DATA_2006_OTHER 0
DATA_LOAD DATA_2006 DATA_2006_NO_REGION 0
DATA_LOAD DATA_2006 DATA_2006_EAST_01 2
DATA_LOAD DATA_2006 DATA_2006_SOUTH_01 1
DATA_LOAD DATA_2006 DATA_2006_WEST_01 1
DATA_LOAD DATA_2006 DATA_2006_NORTH_01 1
DATA_LOAD DATA_2007 DATA_2007_OTHER 0
DATA_LOAD DATA_2007 DATA_2007_NO_REGION 0
DATA_LOAD DATA_2007 DATA_2007_EAST_01 0
DATA_LOAD DATA_2007 DATA_2007_SOUTH_01 0
DATA_LOAD DATA_2007 DATA_2007_WEST_01 0
DATA_LOAD DATA_2007 DATA_2007_NORTH_01 0
DATA_LOAD DATA_2008 DATA_2008_OTHER 0
DATA_LOAD DATA_2008 DATA_2008_NO_REGION 0
DATA_LOAD DATA_2008 DATA_2008_EAST_01 0
DATA_LOAD DATA_2008 DATA_2008_SOUTH_01 0
DATA_LOAD DATA_2008 DATA_2008_WEST_01 0
DATA_LOAD DATA_2008 DATA_2008_NORTH_01 1
DATA_LOAD DATA_2009 DATA_2009_OTHER 0
DATA_LOAD DATA_2009 DATA_2009_NO_REGION 0
DATA_LOAD DATA_2009 DATA_2009_EAST_01 0
DATA_LOAD DATA_2009 DATA_2009_SOUTH_01 0
DATA_LOAD DATA_2009 DATA_2009_WEST_01 0
DATA_LOAD DATA_2009 DATA_2009_NORTH_01 1
24 rows selected.

Now lets create the table to exchange the partition with:

SQL> create table DATA_2006 as select * from DATA_LOAD where 1=2;
Table created.
Now to exchange the partition DATA_2006, with the table DATA_2006:

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes
*
ERROR at line 1:
ORA-14291: cannot EXCHANGE a composite partition with a non-partitioned table

SQL> !oerr ora 14291
14291, 00000, "cannot EXCHANGE a composite partition with a non-partitioned table"
// *Cause: A composite partition can only be exchanged with a partitioned
// table.
// *Action: Ensure that the table being exchanged is partitioned or that
// that the partition being exchanged is non-composite.


This should that we cannot exchange partition with a normal table like we did the last time, now lets create the correct table:

SQL> drop table DATA_2006;
Table dropped.

We need to create a table with the partition scheme as the subpartition scheme of the main/big table, to be able to exchange its partition with a new table:

SQL> CREATE TABLE DATA_2006
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY LIST (state)
8 (PARTITION NORTH_01 VALUES ('DELHI', 'JAMMU KASHMIR', 'PUNJAB', 'HARYANA') TABLESPACE DATA_01,
9 PARTITION WEST_01 VALUES ('RAJASTHAN', 'GUJRAT', 'MAHARASTRA') TABLESPACE DATA_02,
10 PARTITION SOUTH_01 VALUES ('TAMIL NADU', 'KERELA', 'KARNATAKA') TABLESPACE DATA_03,
11 PARTITION EAST_01 VALUES ('ASSAM', 'WEST BENGAL', 'NAGALAND') TABLESPACE DATA_04,
12 PARTITION NO_REGION VALUES (NULL) TABLESPACE USERS,
13 PARTITION OTHER VALUES (DEFAULT) TABLESPACE USERS
14 );
Table created.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

Just to check how the partitions are laid out on the new table. You can see that the subpartition data has moved to the respective partitions of the exchanged table.

SQL> select * from DATA_2006;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_2006';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_2006 NORTH_01 1 0
DATA_2006 WEST_01 1 0
DATA_2006 SOUTH_01 1 0
DATA_2006 EAST_01 2 0
DATA_2006 NO_REGION 0 0
DATA_2006 OTHER 0 0
6 rows selected.

The main/big table will still have the partition defination but with no rows after we analyze the table. Now we can either leave this empty partition or drop the same.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 0 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

We can restore the data back into the main table also. We can exchange the this newly created table with the empty partition in the big/main table back, incase we ever need the data back into the main table. Hence if we archive data by exchange partition we can restore them back into the main table if we ever need it. Another thing is if we might need to restore we should not drop the empty partitions as its easier to restore the data, else we would need to do a split partition to create the partitions again.

SQL> alter table DATA_LOAD EXCHANGE PARTITION DATA_2006 with table DATA_2006 without validation update global indexes;
Table altered.

SQL> select * from DATA_2006;
no rows selected

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE DATA_SIZE STATE
---------- ---------- --------- ---------- --------------------
1 1 30-JAN-06 1 DELHI
2 2 10-MAY-06 1 RAJASTHAN
3 3 18-AUG-06 1 KERELA
4 4 22-OCT-05 1 ASSAM
5 5 26-NOV-06 1 NAGALAND
6 6 18-JUL-08 1 PUNJAB
7 7 14-MAY-09 1 HARYANA
7 rows selected.

SQL> select TABLE_NAME,PARTITION_NAME,num_rows,SUBPARTITION_COUNT from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --------------- ---------- ------------------
DATA_LOAD DATA_2006 5 6
DATA_LOAD DATA_2007 0 6
DATA_LOAD DATA_2008 1 6
DATA_LOAD DATA_2009 1 6

Useful metalink notes:
Exchange Partitions - Common Problems, Doc ID: 198120.1

SCRIPT: To Automate the Composite-Partitioned Table Exchange, Doc ID: 100701.1

Exchange Partition Having Global Index with Non - Partitioned Table, Doc ID: 419505.1

Note: When we do exchange partition the local indexes become invalid, hence we would need to rebuild the same. If we provide the update global indexes, the global indexes don't become invalid and hence no need to rebuild them.

Oracle Partitioning - Merge Partition

Oracle allows us to merge 2 partitions into one new partition.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

SQL> alter table DATA_LOAD merge partitions data_01_2009,data_02_2009 into partition data_01_02_2009 TABLESPACE DATA_02;
Table altered.

Merging Subpartitions:

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'))
) ,
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'))
) ,
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)
)
);

SQL> alter table DATA_LOAD merge subpartitions data_03_2009_1,data_03_2009_2 into subpartition data_03_2009_1_2;
Table altered.

Details about Oracle Partition Methods/Types

Oracle Partitioning - Add Partition

We can also add partitions to an already created partition table. Creating a partition table.
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
data_size NUMBER,
state VARCHAR2(20))
PARTITION BY RANGE (data_size)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (40) TABLESPACE DATA_04
);
Table created.

Note: The table should not have a max value partition, else you will need to use split partition instead of add partition. Partition can be added only in the end not in between.

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,20,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'LL');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
insert into DATA_LOAD values (2,2,sysdate-3,50,'LL')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Now when we tried to insert a value higher than the partition bound value we get this error.
Now lets add partition to accomodate this insert statement.

SQL> alter table DATA_LOAD add partition data_05_2009 VALUES LESS THAN (50) TABLESPACE DATA_01;
Table altered.

SQL> alter table DATA_LOAD add partition data_06_2009 VALUES LESS THAN (60) TABLESPACE DATA_02;
Table altered.

SQL> insert into DATA_LOAD values (2,2,sysdate-3,50,'LL');
1 row created.

Same way the partitions can be added for list or hash partition:
Hash partition: 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.

SQL> alter table DATA_LOAD add partition data_05_2009 TABLESPACE DATA_01;
Table altered.


List Partition: 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.

Its important that the table does not have a DEFAULT value partition.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA')
*
ERROR at line 1:
ORA-14323: cannot add partition when DEFAULT partition exists


SQL> drop table DATA_LOAD
2 /
Table dropped.
Alternately, you cn also drop the partition, make sure that the partition does not have any data, rows:

SQL> alter table DATA_LOAD drop partition OTHER;
Table altered.

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)
);
Table created.

SQL> alter table DATA_LOAD add partition NORTH_EAST_01 VALUES ('ARUNACHAL PRADESH','MIZORAM','TRIPURA');
Table altered.

Add subpartition:

Create table first:
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'))
) ,
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'))
) ,
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> alter table DATA_LOAD modify PARTITION data_01_2009 add SUBPARTITION data_01_2009_3 VALUES LESS THAN (TO_DATE('28-JAN-2009','DD-MON-YYYY'));
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'))
*
ERROR at line 1:
ORA-14211: subpartition bound must collate higher than that of the last subpartition

Cant add partition if there is a maxvalye partition. Please make sure there is no rows before dropping.

SQL> alter table DATA_LOAD drop SUBPARTITION data_03_2009_3;
Table altered.

SQL> alter table DATA_LOAD modify PARTITION data_03_2009 add SUBPARTITION data_03_2009_4 VALUES LESS THAN (TO_DATE('28-MAR-2009','DD-MON-YYYY'));
Table altered.

Details about Oracle Partition Methods/Types

Oracle Partitioning - Split Partition

At times when we initially create partitions we make them with a certain data load in mind. But after sometime/years we realize that the partition scheme is becoming a problem and there are certain partitions with more data and this can create performance problems. Here is how we can split the partitions to create smaller partitions or to say create partitions between already existing partitions.
Lets create a partition table first:

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_size)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
12 );
Table created.

Inserting some data into the table:

SQL> insert into DATA_LOAD values (1,1,sysdate-20,9,'DL');
1 row created.
SQL> insert into DATA_LOAD values (2,2,sysdate-9,35,'RJ');
1 row created.
SQL> insert into DATA_LOAD values (3,3,sysdate-8,45,'LL');
1 row created.
SQL> insert into DATA_LOAD values (4,4,sysdate-7,100,'KK');
1 row created.
SQL> insert into DATA_LOAD values (5,5,sysdate-6,200,'JJ');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.
SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009 MAXVALUE 4

Now we can see that the 4th partition has more rows than the the others, so lets try and split the partition in to 2. And lets make a partition from value 30 to 100.

SQL> alter table DATA_LOAD split partition data_04_2009 at (100) into (PARTITION data_04_2009_a, PARTITION data_04_2009_b);
Table altered.

SQL> analyze table DATA_LOAD compute statistics;
Table analyzed.

SQL> select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,num_rows from user_tab_partitions where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
--------------- --------------- --------------- ----------
DATA_LOAD DATA_01_2009 10 1
DATA_LOAD DATA_02_2009 20 0
DATA_LOAD DATA_03_2009 30 0
DATA_LOAD DATA_04_2009_A 100 2
DATA_LOAD DATA_04_2009_B MAXVALUE 2

So you can see the last partition got split into 2 parts.

Some important things to keep in mind before doing a split partition:

1.) SPLIT PARTITION cannot be used for hash partitions or subpartitions.
2.) Partition cannot be split along the already specified high boundry of the partition you are trying to split.

A good metalink note: 199623.1

Details about Oracle Partition Methods/Types

Oracle Partitioning Types - VII (Virtual Column Based Partitioning)

Virtual Column Based Partitioning
As the name suggest oracle allows us to define partitions based on a virtual column of a table. All types of partitioning methods are allowed for virtual columns. The only thing that is not allowed is if the virtual column uses a PL\SQL function in its definition.

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),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL
)
PARTITION BY RANGE (load_date)
SUBPARTITION BY RANGE (diff_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 (10),
SUBPARTITION data_01_2009_2 VALUES LESS THAN (20),
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 (10),
SUBPARTITION data_02_2009_2 VALUES LESS THAN (20),
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 (10),
SUBPARTITION data_03_2009_2 VALUES LESS THAN (20),
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 (10),
SUBPARTITION data_04_2009_2 VALUES LESS THAN (20),
SUBPARTITION data_04_2009_3 VALUES LESS THAN (MAXVALUE)
)
);
Table created.

SQL> insert into DATA_LOAD values (1,2,sysdate-60,sysdate-50,1,'DELHI',DEFAULT);
1 row created.

SQL> insert into DATA_LOAD values (2,3,sysdate-50,sysdate-35,2,'RAJASTHAN',DEFAULT);
1 row created.

SQL> select * from DATA_LOAD;
DATA_ID SOURCE_ID LOAD_DATE TARGET_DA DATA_SIZE STATE DIFF_DATE
---------- ---------- --------- --------- ---------- -------------------- ----------
1 2 09-MAR-09 19-MAR-09 1 DELHI 10
2 3 19-MAR-09 03-APR-09 2 RAJASTHAN 15
3 4 08-APR-09 13-APR-09 3 RAJASTHAN 5
SQL> select TABLE_NAME,COMPOSITE,PARTITION_NAME,NUM_ROWS,subpartition_count from user_tab_partitions where TABLE_NAME='DATA_LOAD';

TABLE_NAME COM PARTITION_NAME NUM_ROWS SUBPARTITION_COUNT
--------------- --- --------------- ---------- ------------------
DATA_LOAD YES DATA_01_2009 0 3
DATA_LOAD YES DATA_02_2009 0 3
DATA_LOAD YES DATA_03_2009 2 3
DATA_LOAD YES DATA_04_2009 1 3

SQL> select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS from USER_TAB_SUBPARTITIONS where TABLE_NAME='DATA_LOAD';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
--------------- --------------- ------------------------------ ----------
DATA_LOAD DATA_01_2009 DATA_01_2009_3 0
DATA_LOAD DATA_01_2009 DATA_01_2009_2 0
DATA_LOAD DATA_01_2009 DATA_01_2009_1 0
DATA_LOAD DATA_02_2009 DATA_02_2009_3 0
DATA_LOAD DATA_02_2009 DATA_02_2009_2 0
DATA_LOAD DATA_02_2009 DATA_02_2009_1 0
DATA_LOAD DATA_03_2009 DATA_03_2009_3 0
DATA_LOAD DATA_03_2009 DATA_03_2009_2 2
DATA_LOAD DATA_03_2009 DATA_03_2009_1 0
DATA_LOAD DATA_04_2009 DATA_04_2009_3 0
DATA_LOAD DATA_04_2009 DATA_04_2009_2 0
DATA_LOAD DATA_04_2009 DATA_04_2009_1 1

So we can see that the data has been sub partitioned based on virtual column.

Another example of normal range partition using the virtual column:
SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
diff_date NUMBER GENERATED ALWAYS AS (target_date-load_date) VIRTUAL)
PARTITION BY RANGE (diff_date)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
Table created.

To see what error we get while using PL\SQL function while creating virtual column and using it as paritioning key:

The Funtion:
SQL> create or replace function add_test (a in number,b in number)
return number
is
c number;
begin
c:=a+b;
RETURN c;
end;
/
Function created.

SQL> CREATE TABLE DATA_LOAD
( DATA_id NUMBER(6),
source_id NUMBER,
load_date DATE,
target_date DATE,
data_size NUMBER,
state VARCHAR2(20),
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
PARTITION BY RANGE (sum_up)
( PARTITION data_01_2009 VALUES LESS THAN (10) TABLESPACE DATA_01,
PARTITION data_02_2009 VALUES LESS THAN (20) TABLESPACE DATA_02,
PARTITION data_03_2009 VALUES LESS THAN (30) TABLESPACE DATA_03,
PARTITION data_04_2009 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA_04
);
sum_up NUMBER GENERATED ALWAYS AS (add_test(source_id,data_size)) VIRTUAL)
*
ERROR at line 8:
ORA-54021: Cannot use PL/SQL expressions in partitioning or subpartitioning columns

SQL> !oerr ora 54021
54021, 0000, "Cannot use PL/SQL expressions in partitioning or subpartitioning columns"
// *Cause: Attempted to partition a table on a virtual column that contained
// PL/SQL expressions.
// *Action: This is not supported.

Part I
Part II
Part III
Part IV
Part V
Part VI

Oracle Partitioning Types - VI (Multiple Column Partition)

Multiple Column Partitioning
As the name suggests we can define the partition key using multiple column in the table.
So basically if there are two columns which you would like to use to define partitioning to facilitate related data to be stored in the same partition. There are be more than 2 columns for defining column keys. The idea is if using the first column key oracle is not able to ascertain which partition it has to go into, it used the second key and like-wise. The reason why oracle is not able to define the correct partition could be because the partition bound values are overlapping, between partitions.

SQL> CREATE TABLE DATA_LOAD
2 ( DATA_id NUMBER(6),
3 source_id NUMBER,
4 load_date DATE,
5 data_size NUMBER,
6 state VARCHAR2(20))
7 PARTITION BY RANGE (data_id,source_id)
8 ( PARTITION data_01_2009 VALUES LESS THAN (10,10) TABLESPACE DATA_01,
9 PARTITION data_02_2009 VALUES LESS THAN (10,20) TABLESPACE DATA_02,
10 PARTITION data_03_2009 VALUES LESS THAN (20,30) TABLESPACE DATA_03,
11 PARTITION data_04_2009 VALUES LESS THAN (30,40) TABLESPACE DATA_04
12 );
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 NO DATA_01_2009 0
DATA_LOAD NO DATA_02_2009 0
DATA_LOAD NO DATA_03_2009 0
DATA_LOAD NO DATA_04_2009 0

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

SQL> insert into DATA_LOAD values (1,15,sysdate-10,11,'KERELA');
1 row created.

SQL> insert into DATA_LOAD values (15,19,sysdate-10,11,'KERELA');
1 row created.

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

SQL> insert into DATA_LOAD values (29,36,sysdate-20,10,'DELHI');
1 row created.

SQL> insert into DATA_LOAD values (10,12,sysdate-10,11,'KERELA');
1 row created.

SQL> commit;
Commit complete.

SQL> analyze table DATA_LOAD 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 2
DATA_LOAD NO DATA_02_2009 1
DATA_LOAD NO DATA_03_2009 1
DATA_LOAD NO DATA_04_2009 2

So you can see that the insert statement no. 2, had the second value as 15, but it went into partition 1, as it was table to decide using the first value. But the last insert statement had the first value as 10, which is the partition bound value for 1st and 2nd partition, so the row used the second value i.e. 12, and inserted the row in partition 2.

Part I
Part II
Part III
Part IV
Part V
Part VII

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 - IV (Interval Partition)

1.) Interval-Partitioned Tables (11g)

Extract from Oracle Documentation
-----------------------------------------------------------------------------------------
The INTERVAL clause of the CREATE TABLE statement establishes interval partitioning for the table. You must specify at least one range partition using the PARTITION clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.
-----------------------------------------------------------------------------------------

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 RANGE (load_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION data_01_2006 VALUES LESS THAN (TO_DATE('01-01-2006','DD-MM-YYYY')) TABLESPACE DATA_01,
PARTITION data_01_2007 VALUES LESS THAN (TO_DATE('01-01-2007','DD-MM-YYYY')) TABLESPACE DATA_02,
PARTITION data_01_2008 VALUES LESS THAN (TO_DATE('01-01-2008','DD-MM-YYYY')) TABLESPACE DATA_03,
PARTITION data_01_2009 VALUES LESS THAN (TO_DATE('01-01-2009','DD-MM-YYYY')) TABLESPACE DATA_04
);

Table created.
Before data insert:
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_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Now we insert data in this table:

insert into DATA_LOAD values (1,1,sysdate,1,'DELHI');
insert into DATA_LOAD values (2,2,sysdate-30,2,'KERELA');
insert into DATA_LOAD values (3,3,sysdate-300,3,'RAJASTHAN');
insert into DATA_LOAD values (4,4,sysdate-600,4,'PUNJAB');
insert into DATA_LOAD values (5,5,sysdate-60,5,'HARYANA');


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_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Adding future rows:

SQL> insert into DATA_LOAD values (6,6,sysdate+300,6,'HARYANA');

1 row created.

SQL> commit;

Commit complete.

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_2006 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2007 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2008 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO DATA_01_2009 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P81 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P82 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P83 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
DATA_LOAD NO SYS_P84 TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.


So we see that the new INTERVAL PARTITIONS are being created for future dates and dates beyond the high value of the last range partition.

Part I
Part II
Part III
Part V
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