Table Partitioning in Oracle:Divide and Conquer!

Partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. As the number of rows in table increases, the performance impacts will increase and Backup and recovery process may take longer time than usual and sql queries that affecting entire table will take much longer time. We can reduce the performance issue causing by large tables through separating the rows of a single table into multiple parts. Dividing a table’s data in this manner is called partitioning the table. The table that is partitioned is called a partitioned table, and the parts are called partitions.

Important note here is that SQL queries and DML statements do not need to be modified in order to access partitioned tables. After partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects. Also, partitioning is entirely transparent to applications.

Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints. But each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Advantages of using Partition’s in Table

• The performance of queries against the tables may improve because Oracle may have to search only one partition (one part of the table) instead of the entire table to resolve a query.
• The table may be easier to manage. Because the partitioned table’s data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.
• Backup and recovery operations may perform better. Because the partitions are smaller than the partitioned table, you may have more options for backing up and recovering the partitions than you would have for a single large table.

Types of Partitioning Methods

1] RANGE Partitioning

The most basic type of partitioning for a table is called range partitioning. Range partitioning divides a table into partitions based on a range of values. You can use one or more columns to define the range specification for the partitions. Oracle automatically uses the upper bound of the next lower VALUES LESS THAN value as the lower bound of a partition.

Example:

CREATE TABLE sales_range
(salesman_id  	 NUMBER(5),
salesman_name    VARCHAR2(30),
sales_amount  	 NUMBER(10),
sales_date   	 DATE)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

2] HASH Partitioning

Under this type of partitioning, the records in a table are partitioned based of a Hash value found in the value of the column that is used for partitioning. “Hash Partitioning” does not have any logical meaning to the partitions as do the range partitioning.

To create a hash partition, use the PARTITION BY HASH clause in place of PARTITION BY RANGE.

Example:

CREATE TABLE sales_hash
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount  NUMBER(10),
week_no       NUMBER(2))
PARTITION BY HASH(salesman_id)
PARTITIONS 4;

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons.

3] List Partitioning

List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way. For a table with a region_state column as the partitioning key, the sales_west partition might contain values like ‘California’, ‘Hawaii’ etc.

Example:

CREATE TABLE sales_list
(salesman_id  NUMBER(5),
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10),
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);

Creating Subpartitions

You can create subpartitions—that is, partitions of partitions. You can use subpartitions to combine all types of partitions: range partitions, list partitions, and hash partitions. For example, you can use hash partitions in combination with range partitions, creating hash partitions of the range partitions. For very large tables, this composite partitioning may be an effective way of separating the data into manageable and tunable divisions.

Indexing a partitioned table:

When you index a table, you can partition the index just as you partition the table itself. This works because indexes are separate database objects. Partitioning the index generates the same benefits as a partitioned table–improved performance, reduced maintenance time, and increased availability. Many applications use a concept called equipartitioning to increase the total value of partitioned tables and indexes. With equipartitioning, you have the same partitions for an index as you have for its table–the same number, partitioning columns, and partition bounds. A partitioned table can have both partitioned and non-partitioned indexes on it.

Modify characteristics of partitions:

Add a partition	ALTER TABLE ADD PARTITION partition_name VALUES LESS THAN value storage_parameters
Move a partition	ALTER TABLE MOVE PARTITION tablespace
Rename a partition	ALTER TABLE RENAME PARTITION original_partition_name TO new_partition_name
Modify a partition	ALTER TABLE MODIFY PARTITION
Drop a partition	ALTER TABLE DROP PARTITION partition_name
Truncate a partition	ALTER TABLE TRUNCATE PARTITION partition_name

The use of partition-extended table names has the following restrictions:

• A partition-extended table name cannot refer to a remote schema object.
• The partition-extended table name syntax is not supported by PL/SQL.
• A partition extension must be specified with a base table. No synonyms, views, or any other schema objects are allowed.