How to Create and Manage Partition Tables
Now a days
enterprises run databases of hundred of Gigabytes in size. These
databases are known as
Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature
partitioning i.e. you can partition a table according to some criteria .
For example you
SALES table with the following structure
this table contains millions of records, but all the records belong to
four years only i.e. 1991, 1992, 1993
and 1994. And most of the time you are concerned about only one year
i.e. you give queries like the following
select sum(amt) from sales where
select product,sum(amt) from
sales where year=1992
Group by product;
whenever you give queries like this Oracle will search the whole table.
If you partition this table according to
year, then the performance is improve since oracle will scan only a
single partition instead of whole table.
CREATING PARTITION TABLES
To create a
partition table give the following statement
create table sales (year
partition by range (year)
partition p1 values less
than (1992) tablespace u1,
partition p2 values less
than (1993) tablespace u2,
partition p3 values less
than (1994) tablespace u3,
partition p4 values less
than (1995) tablespace u4,
partition p5 values less than
(MAXVALUE) tablespace u5;
above example sales table is created with 5 partitions. Partition p1
will contain rows of year 1991 and
it will be stored in tablespace u1. Partition p2 will contain rows of
year 1992 and it will be stored in tablespace u2.
Similarly p3 and p4.
above example if you don’t specify the partition p4 with values less
than MAVALUE, then you will not be
able to insert any row with year above 1994.
not required, you can place partitions in different tablespaces. If you
place partitions in different tablespaces
then you can isolate problems due to failures as only a particular
partition will not be available and rest of the
partitions will still be available.
example the table is partition by range.
you can partition a table by
of partitioning is useful when dealing with data that has logical ranges
into which it can be distributed;
for example, value of year. Performance is best when the data evenly
distributes across the range
partitioning if your data does not easily lend itself to range
partitioning, but you would like to partition for
performance and manageability reasons. Hash partitioning provides a
method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on
a hash value of the partitioning key
following example shows how to create a hash partition table.
The following example creates a hash-partitioned table. The partitioning
four partitions are created
and assigned system generated names, and they are placed in four named
(tab1, tab2, tab3, tab4);
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally
The following example creates a table with list partitioning
Create table customers (custcode number(5),
Partition by list (city),
Partition north_India values (‘DELHI’,’CHANDIGARH’),
Partition east_India values (‘KOLKOTA’,’PATNA’),
Partition south_India values (‘HYDERABAD’,’BANGALORE’,
Partition west India values (‘BOMBAY’,’GOA’);
If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.
Composite partitioning partitions data using the range method, and
within each partition, subpartitions it using
the hash method. Composite partitions are ideal for both historical data
and striping, and provide improved
manageability of range partitioning and data placement, as well as the
parallelism advantages of hash partitioning.
When creating composite partitions, you specify the following:
- Partitioning method: range
- Partitioning column(s)
- Partition descriptions identifying partition
- Subpartitioning method: hash
- Subpartitioning column(s)
- Number of subpartitions for each partition or
descriptions of subpartitions
The following statement creates a composite-partitioned table. In this
example, three range partitions are created, each
containing eight subpartitions. Because the subpartitions are not named,
system generated names are assigned, but the
clause distributes them across the 4 specified tablespaces (
CREATE TABLE PRODUCTS (partno NUMBER,
PARTITION BY RANGE (partno)
SUBPARTITION BY HASH(description)
SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)
(PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
ALTERING PARTITION TABLES
To add a partition
You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition
at the beginning or in the middle of a table, use the
SPLIT PARTITION clause.
For example to add a partition to sales table give the following command.
alter table sales add partition p6 values less than (1996);
To add a partition to a Hash Partition table give the following command.
Alter table products add partition;
Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
To add a partition by user define name and in your specified tablespace give the following command.
Alter table products add partition p5 tablespace u5;
To add a partition to a List partition table give the following command.
alter table customers add partition central_India
Any value in the set of literal values that describe the partition(s)
being added must not exist in any of the other partitions
of the table.
Coalescing partitions is a way of reducing the number of
partitions in a hash-partitioned table, or the number of subpartitions
in a composite-partitioned table. When a hash partition is coalesced,
its contents are redistributed into one or more remaining partitions
determined by the hash function. The specific partition that is
coalesced is selected by Oracle, and is dropped after its contents have
To coalesce a hash partition give the following statement.
products coalesce partition;
This reduces by one the number of partitions in the table
To drop a partition from Range Partition table, List
Partition or Composite Partition table give the following command.
Alter table sales
drop partition p5;
Once you have drop the partition and if you have created a global index
on the table. Then you have to rebuild the global index after dropping
the partition by giving the following statement.
To avoid rebuilding of indexes after dropping of the partitions you can
also first delete all the records and then drop
the partition like this
Delete from sales
Alter table sales
drop partition p4;
This method is most appropriate for small tables, or for
large tables when the partition being dropped contains a small
percentage of the total data in the table.
Another method of dropping partitions is give the following statement.
ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;
This causes the global index to be updated at the time the partition is dropped.
Exchanging a Range, Hash, or List Partition
To exchange a partition of a range, hash, or
list-partitioned table with a nonpartitioned table, or the reverse, use
ALTER TABLE ... EXCHANGE PARTITION
statement. An example of converting a partition into a nonpartitioned
table follows. In this example, table
stocks can be range, hash,
or list partitioned.
ALTER TABLE stocks
EXCHANGE PARTITION p3 WITH stock_table_3;
ALTER TABLE ... MERGE PARTITIONS
statement to merge the contents of two partitions into one partition. Te
two original partitions are dropped, as are any corresponding local
You cannot use this statement for a hash-partitioned table
or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot
merge non adjacent partitions.
For example the merge the partition p2 and p3 into one
partition p23 give the following statement.
Alter table sales merge partition
p2 and p3 into
Modifying Partitions: Adding Values
MODIFY PARTITION ... ADD VALUES
clause of the
ALTER TABLE statement to
extend the value list of an existing partition. Literal values being
added must not have been included in any other partition's value list.
The partition value list for any corresponding local index partition is
correspondingly extended, and any global index, or global or local index
partitions, remain usable.
The following statement adds a new set of cities (
'MANGALORE') to an existing partition list.
ALTER TABLE customers
MODIFY PARTITION south_india
ADD VALUES ('KOCHI', 'MANGALORE');
Modifying Partitions: Dropping Values
MODIFY PARTITION ... DROP VALUES
clause of the
ALTER TABLE statement to
remove literal values from the value list of an existing partition. The
statement is always executed with validation, meaning that it checks to
see if any rows exist in the partition that correspond to the set of
values being dropped. If any such rows are found then Oracle returns an
error message and the operation fails. When necessary, use a
DELETE statement to delete
corresponding rows from the table before attempting to drop values.
You cannot drop all literal values from the value list
describing the partition. You must use the
ALTER TABLE ... DROP PARTITION
The partition value list for any corresponding local index
partition reflects the new value list, and any global index, or global
or local index partitions, remain usable.
The statement below drops a set of cities (‘
'MANGALORE') from an existing partition value list.
ALTER TABLE customers
MODIFY PARTITION south_india
DROP VALUES (‘KOCHI’,’MANGALORE’);
You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command.
Alter table sales split partition p5 into
(Partition p6 values less than (1996),
Partition p7 values less then (MAXVALUE));
Truncating a partition will delete all rows from the partition.
To truncate a partition give the following statement
Alter table sales truncate partition p5;
LISTING INFORMATION ABOUT PARTITION TABLES
To see how many partitioned tables are there in your schema give the following statement
Select * from user_part_tables;
To see on partition level partitioning information
Select * from user_tab_partitions;