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