Partitioning Methods - Oracle 10g

There are several partitioning methods offered by Oracle Database:

  • Range partitioning
  • Hash partitioning
  • List partitioning
  • Composite range-hash partitioning
  • Composite range-list partitioning

Indexes, as well as tables, can be partitioned. A global index can be partitioned by the range or hash method, and it can be defined on any type of partitioned, or nonpartitioned, table. It can require more maintenance than a local index.

A local index is constructed so that it reflects the structure of the underlying table. It is equipartitioned with the underlying table, meaning that it is partitioned on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table. For local indexes, index partitioning is maintained automatically when partitions are affected by maintenance activity. This ensures that the index remains equipartitioned with the underlying table.

The following sections can help you decide on a partitioning method appropriate for your needs:

  • When to Use Range Partitioning
  • When to Use Hash Partitioning
  • When to Use List Partitioning
  • When to Use Composite Range-Hash Partitioning
  • When to Use Composite Range-List Partitioning

When to Use Range Partitioning

Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning. When creating range partitions, you must specify:

  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds

The example below creates a table of four partitions, one for each quarter of sales. The columns sale_year, sale_month, and sale_day are the partitioning columns, while their values constitute the partitioning key of a specific row. The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition. Each partition is given a name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa, tsb, ...).


A row with sale_year=1999, sale_month=8, and sale_day=1 has a partitioning key of (1999, 8, 1) and would be stored in partition sales_q3.

When to Use Hash Partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key. Creating and using hash partitions gives you a highly tunable method of data placement, because `+7-you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).

To create hash partitions you specify the following:

  • Partitioning method: hash
  • Partitioning column(s)
  • Number of partitions or individual partition descriptions

The following example creates a hash-partitioned table. The partitioning column is id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, ...).


When to Use List Partitioning

Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.

The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning because:

  • Range partitioning assumes a natural range of values for the partitioning column. It is not possible to group together out-of-range values partitions.
  • Hash partitioning allows no control over the distribution of data because the data is distributed over the various partitions using the system hash function.

Again, this makes it impossible to logically group together discrete values for the partitioning columns into partitions. Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally.

Unlike the range and hash partitioning methods, multicolumn partitioning is not supported for list partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of the table. Otherwise all columns that can be partitioned by the range or hash methods can be partitioned by the list partitioning method. When creating list partitions, you must specify:

  • Partitioning method: list
  • Partitioning column
  • Partition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the partitioning column that qualify a row to be included in the partition

The following example creates a list-partitioned table. It creates table q1 _sales _by _region which is partitioned by regions consisting of groups of states.


A row is mapped to a partition by checking whether the value of the partitioning column for a row matches a value in the value list that describes the partition.

For example, some sample rows are inserted as follows:

  • (10, 'accounting', 100, 'WA') maps to partition q1_northwest
  • (20, 'R&D', 150, 'OR') maps to partition q1_northwest
  • (30, 'sales', 100, 'FL') maps to partition q1_southeast
  • (40, 'HR', 10, 'TX') maps to partition q1_southwest
  • (50, 'systems engineering', 10, 'CA') does not map to any partition in the table and raises an error

Unlike range partitioning, with list partitioning, there is no apparent sense of order between partitions. You can also specify a default partition into which rows that do not map to any other partition are mapped. If a default partition were specified in the preceding example, the state CA would map to that partition.

When to Use Composite Range-Hash Partitioning

Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

When creating range-hash partitions, you specify the following:

  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds
  • Subpartitioning method: hash
  • Subpartitioning column(s)
  • Number of subpartitions for each partition or descriptions of subpartitions

The following statement creates a range-hash partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ...,ts4).

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range- partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace.

When to Use Composite Range-List Partitioning

Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but lets you further group the rows of data based on unordered or unrelated column values. When creating range- list partitions, you specify the following:

  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds
  • Subpartitioning method: list
  • Subpartitioning column
  • Subpartition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the subpartitioning column that qualify a row to be included in the subpartition

The following example illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.


A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value. For example, some sample rows are inserted as follows:

  • (10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition q1_1999_ northwest
  • (20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition q2_1999_northwest
  • (30, 9977612, ,'07-Sep-1999', 30987.90, 'FL') maps to subpartition q3_1999_southeast
  • (40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition q4_1999_southcentral
  • (40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and raises an error
  • (50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and raises an error

The partitions of a range- list partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics