Oracle Partitioning - Partition Types

Oracle Partitioning - Partition Types

Oracle Partitioning is the splitting of data groups belonging to the same table into separate physical areas using separate partition tablespaces. Partitions can actually coexist in the same physical data file, but the same effect of partitioning might not be achieved. Separate partitions can be accessed in Parallel or individually or in groups.

How can partitioning improve the performance of Sql queries ?
Partitioning can be used to break large tables into smaller subsets.Processing of small subsets individually or in parallel ways is much faster than executing serial procession on large data sets. Partitioning is the most useful in very large database applications.

Benefits of Oracle Partitioning

• Parallel processing is a way of splitting data into separately located physical files. Separating table and index datafiles onto separate disks in a nonpartitioned database is a form of simple partitioning. It is often highly beneficial to read index and data spaces from separate locations because table and index physical spaces are often read almost in parallel.Partitioning can enhance the benefits of splitting datafiles.
• Different partitions within the same table can have different physical storage structures. Different partitions within a table can even be
  both read-write and read only, perhaps allowing separation of archived and current data.
• Backup, recovery, and utility usage (SQL*Loader, Export, andImport) can utilize parallel processing and activities on individual partitions.

The Optimizer can access individual partitions when processing SQL code. This process is termed partition pruning because partitions can potentially be removed from the database read required by a query. Additionally, the Optimizer can execute against multiple partitions using parallel processing, generally on high-end multiple CPU server platforms, with datafiles spread across multiple disks

What is manual Partition Pruning ?

Exclusively instructing the optimizer to use a specified partition only to retrived the data based on partition name.

Partitions can be created on single or multiple columns of a table. A tablecan be divided into separate partitions based on three methods: (1) ranges of values, (2) values in lists, and (3) hashing algorithms in columns. Additionally,partitions can be one of two composites of the already mentioned three partitioning methods.

A partition is divided based on what is called a partition key. This key is internal to Oracle Database. The partition key is the data definition splitting table rows into separate partitions. For example, a range partition on a table could have rows separated into different partitions based on a date for each row, perhaps dividing financial data into quarters. The partition key is that date column, as defined by the range partition.
What are the specifics of the different partitioning methods?

Range Partition : The most used form of Partitioning- This splits rows in a table based on ranges of values (e.g., splitting a table of transactions into periods, such as the four quarters in a year, where the four quarters would be derived from a transaction date).

List partition: This splits rows based on lists of values, dividing rows into separate partitions based on matches between list entries and row column values. For example, a table containing state codes such as NY (New York) and CA (California) could be split up into separate partitions for each state.

Hash partition: A hashing algorithm separates rows based on a column specification into a specified number of separate partitions. The hash value is calculated internally from the partition key. This partitioning method splits the number of rows in a table evenly across all partitions.

Composite partition: Partitions can contain subpartitions of two types: (1) a range partition can contain multiple hash subpartitions, and (2) a range partition can contain multiple list subpartitions