SEGMENTS IV - PARTITIONED TABLES

Partitioned Tables

Introduced with Oracle8, Oracle partitioning has matured over the years, through Oracle8i and Oracle9i.

From the stage of stripping the datafiles across the disks for better IO, Oracle has enabled the customers to partition large tables and associate those partitions with a tablespace of choice and the datafiles can be across different physical disks which are existing before oracle has come up on that server with which the disks are associated. This reduces IO contention and fast accesses. But by design if all the partitions are created on a single disk Oracle may not perform, as you want as there can be IO contention. The balancing of the IO also plays definite role in gaining the performance. The partitioning also helps improve the availability by bringing down the downtime. Improves data access methodology

Designing Partitioned Tables

Options of partitioning available

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

When to use range-partitioning technique?

Oracle says:

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

Deciding the Partition Key Column in range-partitioning

The most generally called for column, which eliminates the full table scan and depends on a sub-set of contiguous data, is to be used as the partition key.
01. The partition key need not be unique.
02. The partition key can be a multi columnar key
03. Low cardinality helps improve performance
04. Predicate should help prune partitions by eliminating un-used partitions form data access
Rules that one has to remember when doing range partitioning:

Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound for the partitions. Any binary values of the partition key equal to or higher than this literal are added to the next higher partition.

All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.

When to Use the Hash Partitioning technique?

Oracle says:

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 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 columns(s)
Number of partitions or individual partition descriptions

Deciding the Partition Key Column in range-partitioning

A primary key on the table is a good candidate for hash partitioning the table.
Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning. It does this with a simple syntax and is easy to implement. It is a better choice than range partitioning when:

You do not know beforehand how much data maps into a given range
The sizes of range partitions would differ quite substantially or would be difficult to balance manually. Range partitioning would cause the data to be undesirably clustered. Performance features such as parallel DML, partition pruning, and partition-wise joins are important.

Rules of the Game for hash partitioning

For hash partitioning, you need only specify the number of partitions and where you want them to be physically placed; Oracle Database then automatically inserts rows into partitions based on hash of the partition key. It is important to specify a power of 2 for the number of hash partitions (define 2n partitions); otherwise uneven distributions of rows may occur. You also specify the storage for the whole table and the tablespace for the partitions. Local indexes are always equi-partitioned (on the same partitioning key and partition break points).

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Alternatively, you can use PARTITION clauses to name the individual partitions and their tablespaces.

The only attribute you can specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (except TABLESPACE), which are inherited from the table level.

To create hash partitions you specify the following:
Partitioning method: hash
Partitioning columns(s)
Number of partitions or individual partition descriptions

When to use Hash Partitioning:

Oracle says

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 you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).

List Partitioning

Oracle says

The semantics for creating list partitions are very similar to those for creating range partitions. However, to create list partitions, you specify a PARTITION BY LIST clause in the CREATE TABLE statement, and the PARTITION clauses specify lists of literal values, which are the discrete values of the partitioning columns that qualify rows to be included in the partition.

For list partitioning, the partitioning key can only be a single column name from the table.

Note that you cannot have multi columnar list partitioning.

List partitioning gives data warehouse administrators precise control over which data belongs in each partition. For each partition, the data warehouse administrator can specify a list of values for its partitioning key. Controlling data placement by partition helps database administrators achieves the best combination of manageability and performance. In particular, list partitioning offers advantages in certain data warehouse environments.

List partitioning complements the functionality of range partitioning, which is often used in data warehouses. Range partitioning is useful for segmenting a table along a continuous domain, such as time. Tables in data warehouses are often range-partitioned by time, so that each range partition contains the data for a given range of time values such as one partition per month or per week. In contrast, list partitioning is useful for segmenting a table along a discrete domain, such as products. Each partition in a list-partitioned table contains data matching a list of discrete partition-key values.

Available only with list partitioning, you can use the keyword DEFAULT to describe the value list for a partition. This identifies a partition that will accommodate rows that do not map into any of the other partitions.

Like for range partitions, optional sub-clauses of a PARTITION clause can specify physical and other attributes specific to a partition's segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

One of the interesting things to note about list partitioning is that there is no apparent sense of ordering between partitions (unlike range partitioning). 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 above example, the state CA would map to that partition.

Hybrid or composite partitioning techniques available:

Hybrid or Composite partitioning is a combination of range and hash partitioning that affords more granular partition elimination of data. First, you use a set of columns as a range-partition key (time or geography are often good choices). Next, you need to compute a hash value on a different set of columns that are as evenly distributed as possible (using the primary key is usually best).

01. Composite Range-Hash Partitioned Tables

To create a range-hash partitioned table, you start by using the PARTITION BY RANGE clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY HASH clause that follows similar syntax and rules as the PARTITION BY HASH clause. The individual PARTITION and SUBPARTITION or SUBPARTITIONS clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

Attributes specified for a range partition apply to all sub-partitions of that partition. You can specify different attributes for each range partition, and you can specify a STORE IN clause at the partition level if the list of tablespaces across which that partition's sub-partitions should be spread is different from those of other partitions.

02. Composite Range-List Partitioned Tables

The concept of range-list partitioning is similar to that of the other composite partitioning method, range-hash, but this time you specify that the sub-partitions are to be list rather than hash. Specifically, after the CREATE TABLE ... PARTITION BY RANGE clause, you include a SUBPARTITION BY LIST clause that follows similar syntax and rules as the PARTITION BY LIST clause. The individual PARTITION and SUBPARTITION clauses, and optionally a SUBPARTITION TEMPLATE clause, follow.

The range partitions of the composite partitioned table are described as for non-composite range partitioned tables. This allows that optional subclauses of a PARTITION clause can specify physical and other attributes, including tablespace, specific to a partition's segment. If not overridden at the partition level, partitions inherit the attributes of their underlying table.

The list sub-partition's descriptions, in the SUBPARTITION clauses, are described as for non-composite list partitions, except the only physical attribute that can be specified is a tablespace (optional). Sub-partitions inherit all other physical attributes from the partition description.