Very Large Databases use partitioning to improve the ability to support large data sets.  Breaking large tables into smaller manageable chunks.  This allows for partition pruning, which prevents access to partitions which will not have data queried.  Partitioning and indexing work together to provide parallel processing to search multiple partitions at same time as in data ware housing.

 

Partitioning Methods: 

You can partition data into a single level or two level for a composite partition.  How the data is accessed determines the best implementation method.   Regardless of partitioning method, you have three ways to control distributing of partitions:

1. Range
2. Hash
3. List

 

Single Level Partitioning

One or more of the columns acts as the partition key spitting the table into multiple sections.

1. Range Partitioning – maps data to a range of values in the partition key. Each row is identified with a mapping to partition key.   This is the most common partitioning method and is commonly used with dates.  Partition is created with a VALUES LESS THEN clause which show the upper bound of a date column.  The highest partition can use a MAXVALUE to provide an upper limit.
2. Hash Partitioning – This method uses a hashing algorithm to map data to partitioning key. Used to evenly spread data among partitions. It is easier to implement then range.  Good to use if no date column exists and no obvious partitioning key exists.
3. List Partitioning – Allows you to provide a list of values for partitioning key column and divides data base on list. You control exactly how rows are mapped to partitions.  You list a set of values to be placed in separate partitions and use the DEFAULT clause to specify a partition for any value not in the list.

 

Composite Partitions

This combines the basic partitioning methods into multiple sub-partitions of the same method or different methods.  Provides for a finer level of sub-division of data.   Possible composite partitions are:

• Range-Range
• Range-Hash
• Range-List
• List-Range
• List-Hash
• List-List
• Hash-Hash

 

 

Larry Catt

OCP