Partitioning: Partition Decisions

Partitioning is a tool. 

It is a tool to make administration easier (divide and conquer)

It is a tool to make availability higher (one of your partitions could take a hit and the 

others stay up and running while you repair one.   )

It is a tool that on a transactional system with lots of indexed reads you need to employ carefully to ensure you do not NEGATIVELY impact performance.

It is a tool on a data-warehouse with lots of "big operations" (full scans for example) you can use to significantly reduce run times (assuming you've DESIGNED to exploit that of course).

Your transactional system with this keyed read may well now benefit from concurrent inserts/updates and deletes -- given that you have N index segments (n root blocks to contend over, n branch blocks to contend over -- not just ONE) and N table segments (N freelists at least, increased concurrency).  

You have to be careful in many cases that partitioning doesn't KILL YOU performance wise.

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:14188311731917

Oracle supports a wide array of partitioning methods:

• Range Partitioning - the data is distributed based on a range of values.
• List Partitioning The data distribution is defined by a discrete list of values. One or multiple columns can be used as partition key.
• Auto-List Partitioning - extends the capabilities of the list method by automatically defining new partitions for any new partition key values.
• Hash Partitioning - an internal hash algorithm is applied to the partitioning key to determine the partition.
• Composite Partitioning - combinations of two data distribution methods are used. First, the table is partitioned by data distribution method one and then each partition is further subdivided into subpartitions using the second data distribution method.
• Multi-Column Range Partitioning - an option for when the partitioning key is composed of several columns and subsequent columns define a higher level of granularity than the preceding ones.
• Interval Partitioning - extends the capabilities of the range method by automatically defining equi-partitioned ranges for any future partitions using an interval definition as part of the table metadata.
• Reference Partitioning Partitions - a table by leveraging an existing parent-child relationship. The primary key relationship is used to inherit the partitioning strategy of the parent table to its child table.
• Virtual Column Based Partitioning - allows the partitioning key to be an expression, using one or more existing columns of a table, and storing the expression as metadata only.
• Interval Reference Partitioning - an extension to reference partitioning that allows the use of interval partitioned tables as parent tables for reference partitioning.

A hybrid partitioned table is a feature in Oracle that allows partitions to reside in both internal and external sources. Internal partitions are in database data files, while external partitions are in external files and sources.