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
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;