Composite range-hash partitioning is particularly common for tables that store history, are very large consequently, and are frequently joined with other large tables. For these types of tables (typical of data warehouse systems), composite range-hash partitioning provides the benefit of partition pruning at the range level with the opportunity to perform parallel full or partial partition-wise joins at the hash level. Specific cases can benefit from partition pruning on both dimensions for specific SQL statements.
Composite range-hash partitioning can also be used for tables that traditionally use hash partitioning, but also use a rolling window approach. Over time, data can be moved from one storage tier to another storage tier, compressed, stored in a read-only tablespace, and eventually purged. Information Lifecycle Management (ILM) scenarios often use range partitions to implement a tiered storage approach. See Managing and Maintaining Time-Based Information for more details.
Example 3-8 is an example of a range hash partitioned page_history table of an Internet service provider. The table definition is optimized for historical analysis for either specific client_ip values (in which case queries benefit from partition pruning) or for analysis across many IP addresses, in which case queries can take advantage of full or partial partition-wise joins.
This example shows the use of interval partitioning. You can use interval partitioning in addition to range partitioning so that interval partitions are created automatically as data is inserted into the table.
Example 3-8 Creating a table with composite range-hash partitioning
CREATE TABLE page_history
( id NUMBER NOT NULL
, url VARCHAR2(300) NOT NULL
, view_date DATE NOT NULL
, client_ip VARCHAR2(23) NOT NULL
, from_url VARCHAR2(300)
, to_url VARCHAR2(300)
, timing_in_seconds NUMBER
) PARTITION BY RANGE(view_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY HASH(client_ip)
SUBPARTITIONS 32
(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2006','dd-MON-yyyy')))
PARALLEL 32 COMPRESS;