Composite list-hash partitioning is useful for large tables that are usually accessed on one dimension, but (due to their size) still must take advantage of parallel full or partial partition-wise joins on another dimension in joins with other large tables.
Example 3-11 shows a credit_card_accounts table. The table is list-partitioned on region in order for account managers to quickly access accounts in their region. The subpartitioning strategy is hash on customer_id so that queries against the transactions table, which is subpartitioned on customer_id, can take advantage of full partition-wise joins. Joins with the hash partitioned customers table can also benefit from full partition-wise joins. The table has a local bitmap index on the is_active column.
Example 3-11 Creating a table with composite list-hash partitioning
CREATE TABLE credit_card_accounts
( account_number NUMBER(16) NOT NULL
, customer_id NUMBER NOT NULL
, customer_region VARCHAR2(2) NOT NULL
, is_active VARCHAR2(1) NOT NULL
, date_opened DATE NOT NULL
) PARTITION BY LIST (customer_region)
SUBPARTITION BY HASH (customer_id)
SUBPARTITIONS 16
( PARTITION emea VALUES ('EU','ME','AF')
, PARTITION amer VALUES ('NA','LA')
, PARTITION apac VALUES ('SA','AU','NZ','IN','CH')
) PARALLEL;
CREATE BITMAP INDEX is_active_bix ON credit_card_accounts(is_active)
LOCAL PARALLEL NOLOGGING;