Manage Table Partitions in Oracle 11g

About Table Partition:
Partitioning addresses key issues in supporting very large tables and indexes by letting you decompose them into smaller and more manageable pieces called partitions. SQL queries and DML statements do not need to be modified in order to access partitioned tables. However, after partitions are defined, DDL statements can access and manipulate individuals 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.

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.

Advantages:
1) Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
2) Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
3) Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.
4) Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
5) Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
6) Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.
7) No. archival required for your table in case of big table and less use of old records.

Note: It is a pricing option with Oracle. Be sure before use. Again it is an enterprise option only.

Useful queries related to table-partition:

-- find all partitions count

select table_owner,
  table_name,
  partition_name,
  segment_created,
  high_value 
from all_tab_partitions
where table_owner in('HR','CRM','PAYROLL','XYZ')
    

-- drop partition (If a partition not in use / wrongly high value given but don't have data in it)
SQL> alter table HR.EMP drop partition EMP_PART_1;

-- find max value of primary key column
select max(EMPid) from HR.EMP # If used sequence based pk

-- find no. of records of a parttion
select count(*) 
from HR.EMP partition(EMP_PART_2)

-- add new partition
alter table HR.EMP 
add PARTITION EMP_PART_3 values less than (1000000)

--  alter table to add new range partition dynamically ( New feature in Oracle 11g onwards)

alter table HR.EMP  set interval (100000);

Click here to read more about Table and Index partitioning