11g feature: Reference Partitioning

Reference Partitioning

Reference partitioning allows the partitioning of a table based on the partitioning scheme of the table referenced in its referential constraint. The partitioning key is resolved through an existing parent-child relationship, enforced by active primary key or foreign key constraints. This means that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, making application development easier and less error-prone.

To create a reference-partitioned table, you specify a PARTITION BY REFERENCE clause in the CREATE TABLE statement. This clause specifies the name of a referential constraint and this constraint becomes the partitioning referential constraint that is used as the basis for reference partitioning in the table.

As with other partitioned tables, you can specify object-level default attributes, and can optionally specify partition descriptors that override the object-level defaults on a per-partition basis.
CREATE TABLE orders
(order_id NUMBER(12),
order_date DATE,
order_mode VARCHAR2(8),
customer_id NUMBER(6),
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(PARTITION Q1_2005 VALUES LESS THAN
(TO_DATE('01-APR-2005','DD-MON-YYYY')),
PARTITION Q2_2005 VALUES LESS THAN
(TO_DATE('01-JUL-2005','DD-MON-YYYY')),
PARTITION Q3_2005 VALUES LESS THAN
(TO_DATE('01-OCT-2005','DD-MON-YYYY')),
PARTITION Q4_2005 VALUES LESS THAN
(TO_DATE('01-JAN-2006','DD-MON-YYYY')));

The example in the slide above creates a list-partitioned table called ORDERS which is range-partitioned on order_date. It is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005. This table will be referenced in the creation of a reference partitioned table on the next slide.
CREATE TABLE order_items
( order_id NUMBER(12) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT order_items_fk
FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

The reference-partitioned child table ORDER_ITEMS example above is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.


If partition descriptors are provided, then the number of partitions described must be exactly equal to the number of partitions or sub-partitions in the referenced table. If the parent table is a composite partitioned table, then the table will have one partition for each sub-partition of its parent; otherwise the table will have one partition for each partition of its parent.

Partition bounds cannot be specified for the partitions of a reference-partitioned table. The partitions of a reference-partitioned table can be named. If a partition is not explicitly named, then it will inherit its name from the corresponding partition in the parent table, unless this inherited name conflicts with one of the explicit names given. In this case, the partition will have a system-generated name.

Partitions of a reference-partitioned table will co-locate with the corresponding partition of the parent table, if no explicit tablespace is specified for the reference-partitioned table's partition.

Hash Partitioning/How Cardinality Affects Distribution

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 and is a better choice than range partitioning when:

a.You do not know beforehand how much data will map into a given range

b.Sizes of range partitions would differ quite substantially

c.Partition pruning and partition-wise joins on a partitioning key are important

d.Range partitioning would cause the data to be undesirably clustered

In 11g hash partitioning can also be used:
a.To enable partial or full parallel partition-wise joins with very likely equi-sized partitions.

b.To distribute data evenly among the nodes of an MPP platform that uses Oracle Real Application Clusters. As a result, you can minimize interconnect traffic
when processing internode parallel statements.

c.To use partition pruning and partition-wise joins according to a partitioning
key that is mostly constrained by a distinct value or value list.

d.To randomly distribute data to avoid I/O bottlenecks if you do not use
a storage management technique that stripes and mirrors across all available
devices.

The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced and with hash partitioning, pruning is only possible using equality or IN-list predicates.

The number of partitions should always be a power of two (2, 4, 8, and so on)to obtain the most even data distribution.

Cardinality is important for choosing a key for hash partitioning. Oracle recommends that you do not use hash partitioning on key columns with low cardinality (columns in which the number of distinct values is small compared to
the number of rows in the table.)

The hash function works best with a LARGE number of values. Rows are mapped into partitions based on the hash value of the partitioning key. A primary key is a very good hash key because it has a wide range a values.

For example, if you had a hash partitioned table with 8 hash partitions, and a key column with only 10 distinct values, you could encounter the following:

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 8;

Partition DEPTNO Column Values
----------- ---------------------
hash_part1 1,2,3
hash_part2 4,5,6,
hash_part3 7
hash_part4 8
hash_part5
hash_part6
hash_part7 9
hash_part8 10

You would have a scenario where some of your hash partitions would contain a high number of distinct values and your hash partitions would be widely different sizes. It is even possible that some hash partitions could contain zero distinct values and others will contain three or more distinct values. One of the reasons to use hash partitioning is to have evenly sized partitions. That's why we recommend that you do not use hash partitioning when low cardinality is a factor.

In general, the hash-partitioning key should be unique or near unique. Since a table may contain several near-unique keys and thus several candidates for hash
partitioning, the other consideration for choosing the partitioning key is the access profile. Hash partitioning can improve the performance of single-key lookups.

For example:
SELECT * FROM emp WHERE empno = 123 <-----single key lookup SELECT * FROM emp WHERE sal > 5000 <------range lookup

If you create a local index for this table, the database constructs the index so that it is equipartitioned with the underlying table.

The database also ensures that the index is maintained automatically when maintenance operations are performed on the underlying table. The following is an example of creating a local index on the table dept: CREATE INDEX loc_dept_ix ON dept(deptno) LOCAL;

You can optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, but if you do not do so, the database uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.

Hash partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP
environments. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

The syntax for creating a hash partitioned global index is similar to that used
for a hash partitioned table. For example, the following statement creates a
hash-partitioned global index:
CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);