Composite Partitioning

Composite partitioning is a combination of range and hash partitioning. Notice in Figure 15.3 that each partition is now subdivided into four additional subpartitions. Listing 15.4 is the syntax used to support Figure 15.3. Each partition named P1, P2, P3, and P4 would contain the range of dates as defined by the STORE IN clause. The SUB PARTITIONS 4 clause subdivides each of these partitions into four logical units. The SUBPARTITION BY HASH clause, on the other hand, equally divides the rows that meet the range criteria, using the ST_CUSTOMER_ID as the hash key to evenly distribute the rows throughout these four subpartitions.

This example gives the ST_SALES object a total of 16 partitions for even row distribution.

Listing 15.4. Sales Tracking ST_SALES Composite Partition Table Example

CREATE TABLE st_sales (sales_customer_id NUMBER(6), sales_sale_date DATE, sales_sale_amt NUMBER(9,2)) PARTITION BY RANGE(sales_sale_date) SUB PARTITION BY HASH (sales_customer_id) SUB PARTITIONS 4 STORE IN (ST_SALES_p1, ST_SALES_p2, ST_SALES_p3, ST_SALES_p4) (PARTITION p1 VALUES LESS THAN ('01-APR-2000'), PARTITION p2 VALUES LESS THAN ('01-JUL-2000'), PARTITION p3 VALUES LESS THAN ('01-OCT-2000'), PARTITION p4 VALUES LESS THAN (MAXVALUE) ); ok pm