The following example illustrates the use of a multicolumn partitioned approach for table supplier_parts
, storing the information about which suppliers deliver which parts. To distribute the data in equal-sized partitions, it is not sufficient to partition the table based on the supplier_id
, because some suppliers might provide hundreds of thousands of parts, while others provide only a few specialty parts. Instead, you partition the table on (supplier_id
, partnum
) to manually enforce equal-sized partitions.
Every row with supplier_id
< 10 is stored in partition p1
, regardless of the partnum
value. The column partnum
is evaluated only if supplier_id
=10, and the corresponding rows are inserted into partition p1
, p2
, or even into p3
when partnum
>=200. To achieve equal-sized partitions for ranges of supplier_parts
, you could choose a composite range-hash partitioned table, range partitioned by supplier_id
, hash subpartitioned by partnum
.
Defining the partition boundaries for multicolumn partitioned tables must obey some rules. For example, consider a table that is range partitioned on three columns a
, b
, and c
. The individual partitions have range values represented as follows:
P0(a0, b0, c0) P1(a1, b1, c1) P2(a2, b2, c2) ... Pn(an, bn, cn)
The range values you provide for each partition must follow these rules:
a0
must be less than or equal to a1
, and a1
must be less than or equal to a2
, and so on.
If a0
=a1
, then b0
must be less than or equal to b1
. If a0
< a1
, then b0
and b1
can have any values. If a0
=a1
and b0
=b1
, then c0
must be less than or equal to c1
. If b0
<b1
, then c0
and c1
can have any values, and so on.
If a1
=a2
, then b1
must be less than or equal to b2
. If a1
<a2
, then b1
and b2
can have any values. If a1
=a2
and b1
=b2
, then c1
must be less than or equal to c2
. If b1
<b2
, then c1
and c2
can have any values, and so on.
CREATE TABLE supplier_parts ( supplier_id NUMBER, partnum NUMBER, price NUMBER) PARTITION BY RANGE (supplier_id, partnum) (PARTITION p1 VALUES LESS THAN (10,100), PARTITION p2 VALUES LESS THAN (10,200), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE));
The following three records are inserted into the table:
INSERT INTO supplier_parts VALUES (5,5, 1000); INSERT INTO supplier_parts VALUES (5,150, 1000); INSERT INTO supplier_parts VALUES (10,100, 1000);
The first two records are inserted into partition p1
, uniquely identified by supplier_id
. However, the third record is inserted into partition p2
; it matches all range boundary values of partition p1
exactly and the database therefore considers the following partition for a match. The value of partnum
satisfies the criteria < 200, so it is inserted into partition p2
.
SELECT * FROM supplier_parts PARTITION (p1); SUPPLIER_ID PARTNUM PRICE ----------- ---------- ---------- 5 5 1000 5 150 1000 SELECT * FROM supplier_parts PARTITION (p2); SUPPLIER_ID PARTNUM PRICE ----------- ---------- ---------- 10 100 1000