Oracle Partitioning Explained -Tables (Part -I)

Last updated on August 16th, 2015 at 03:11 am

WHAT IS Oracle Partitioning?
Partitioning divides a table, index, or index-organized table into smaller components. Each component is called a partition (or subpartition for composite partitioned objects). Every partition has a unique name, and may optionally have individual storage characteristics: examples include compression, or being stored in different tablespaces. A well-designed partitioning strategy can improve query access and updates by limiting the operation to a single or sub-set of partitions.Partitioning for Performance – Partition Pruning
This feature enables the query optimizer to skip partitions that are not required by a particular SQL statement. Depending upon the SQL statement, the optimizer can identify partitions and subpartitions that need to be accessed, as well as ones that do not. This can result in substantial improvements in query performance, because the optimizer focuses on a specific subset of data that can be refined further if additional predicates exist.
The optimizer does this by using the partition information stored in the data dictionary to identify the content of a partition without querying the data it contains. Partitioning is an extra layer of the data dictionary between Tables/Indexes and Tablespaces

Table Partitions
In Oracle Database 11g, a table can have a maximum of 1048575 (1024K – 1) partitions. With the sole exception of tables containing columns of LONG or LONG RAW datatypes, all tables can be partitioned (including columns of type CLOB or BLOB).

we have range,hash,list etc type of partitioning aviable

How to move the existing object to Partitioned object

There are many ways to acheive this feat. I am explaining here the simplistic method
1.Create an empty partitioned table using the partitioned clause and with the parallel option. The table name must have a different name from the non-partitioned table.
2.Populate data for the required partition from the nonpartitioned table.
3. Consider using the APPEND hint with an INSERT as an easy code change that provides good performance. If logging is enabled and indexes are present, the INSERT /*+ APPEND */ hint may not be effective.To minimize the overhead of index maintenance, drop indexes prior to migration and recreate them once the partitioned table has been populated.
4.Rename the partitioned table to the same as the original table, or change the synonym.
5. Build table indexes for the partitioned table


Leave a Reply