Partitioning - Expert Oracle Database Architecture - page 616

Reference partitioning eases the implementation of partitioned tables that are related through referential
integrity constraints. This allows the child table to be logically partitioned in the same manner as the parent table
without having to duplicate parent table columns to the child table.
Interval reference partitioning allows you to combine the interval and reference partitioning features. This
ability is new starting with Oracle 12 c and is useful when you need to use the interval and reference partitioning
features in tandem.
Virtual column partitioning allows you to partition using a virtual column as the key. This feature provides you
the flexibility to partition on a substring of a regular column value (or any other SQL expression). This is useful when
it's not feasible to use an existing column as the partition key, but you can partition on a subset of the value contained
in an existing column.
Composite partitioning is useful when you have something logical by which you can range partition, but the
resulting range partitions are still too large to manage effectively. You can apply the range, list, or hash partitioning
and then further divide each range by a hash function or use lists to partition or even ranges. This will allow you to
spread I/O requests out across many devices in any given large partition. Additionally, you may achieve partition
elimination at three levels now. If you query on the partition key, Oracle is able to eliminate any partitions that do not
meet your criteria. If you add the subpartition key to your query, Oracle can eliminate the other subpartitions within
that partition. If you just query on the subpartition key (not using the partition key), Oracle will query only those hash
or list subpartitions that apply from each partition.
It is recommended that if there is something by which it makes sense to range partition your data, you should
use that over hash or list partitioning. Hash and list partitioning add many of the salient benefits of partitioning, but
they are not as useful as range partitioning when it comes to partition elimination. Using hash or list partitions within
range partitions is advisable when the resulting range partitions are too large to manage or when you want to use all
PDML capabilities or parallel index scanning against a single range partition.

Partitioning Indexes
Indexes, like tables, may be partitioned. There are two possible methods to partition indexes:

• Equipartition the index with the table : This is also known as a local index . For every table
partition, there will be an index partition that indexes just that table partition. All of the entries
in a given index partition point to a single table partition, and all of the rows in a single table
partition are represented in a single index partition.

• Partition the index by range or hash : This is also known as a globally partitioned index . Here
the index is partitioned by range, or optionally in Oracle 10 g and above by hash, and a single
index partition may point to any (and all) table partitions.

Figure 13-5 demonstrates the difference between a local and a global index.

Index Partition A Index Partition B

Index Partition B

Index Partition A

Table
Partition
A

Table
Partition
B

Table
Partition
C

Table
Partition
A

Table
Partition
B

Locally Partitioned Globally Partitioned

Figure 13-5. Local and global index partitions