High Level Steps to consider Partitioning on a table:
- What is the kind of data and purpose ?
- What is the size of partition ?
- What type of partition would be optimal for this data ? Range/Hash/List etc
- Do we have to look for Sub Partitioning if each Partition size is Large
PURPOSE
Provides Navigation to Topics related to Types of Partitioning.
SCOPE
This Document is to be available for those using Oracle Partitioning Feature.
DETAILS
RANGE Partitioning
Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. Range partitioning is the most common type of partitioning and is often used with dates.
Example for Creating And Adding Table and Index Partitions To Ranged Partitioned Table (Doc ID 69715.1)
INTERVAL Partitioning
Interval partitioning is an extension of range partitioning.Interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions.
How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)
LIST Partitioning
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition
HASH Partitioning
Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
How to Implement Hash Partitioning on IOT Tables in 9i & Above (Doc ID 165701.1)
Rows Per Partition Skewed in Hash Partitioned Table (Doc ID 1068430.1)
REFERENCE Partitioning
Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)
SYSTEM Partitioning
System partitioning enables application-controlled partitioning without having the database controlling the data placement.The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application.
COMPOSITE Partitioning
Composite partitioning is a combination of the basic data distribution methods. A table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)
Information in this document applies to any platform.
PURPOSE
Provides Navigation to Topics related to Types of Partitioning.
SCOPE
This Document is to be available for those using Oracle Partitioning Feature.
DETAILS
RANGE Partitioning
Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. Range partitioning is the most common type of partitioning and is often used with dates.
Example for Creating And Adding Table and Index Partitions To Ranged Partitioned Table (Doc ID 69715.1)
INTERVAL Partitioning
Interval partitioning is an extension of range partitioning.Interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions.
How to Use Interval Partitioning with a Rolling Partition Window and Avoid ORA-14758 (Doc ID 1590833.1)
LIST Partitioning
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition
HASH Partitioning
Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
How to Implement Hash Partitioning on IOT Tables in 9i & Above (Doc ID 165701.1)
Rows Per Partition Skewed in Hash Partitioned Table (Doc ID 1068430.1)
REFERENCE Partitioning
Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints. The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns.
How to Create Interval-Reference Partitioned Tables in Oracle 12c (Doc ID 1519042.1)
SYSTEM Partitioning
System partitioning enables application-controlled partitioning without having the database controlling the data placement.The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application.
COMPOSITE Partitioning
Composite partitioning is a combination of the basic data distribution methods. A table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition represent a logical subset of the data.Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning.
Master Note for Partitioning (Doc ID 1312352.1)
Purpose and Benefits/Advantages/Uses of Table Partitioning (Doc ID 1390871.1)
How Should I Partition My Tables? (Doc ID 1563143.1)
Oracle12C: Using SQLAccess Advisor (DBMS_ADVISOR) -- Partition Advisor (Doc ID 1567962.1)
12c Partitioning Enhancements, New Features (Doc ID 1568010.1)
11g Partitioning Enhancements (Doc ID 452447.1)
