Composite list-range partitioning is useful for large tables that are accessed on different dimensions. For the most commonly used dimension, you can specifically map rows to partitions on discrete values. List-range partitioning is commonly used for tables that use range values within a list partition, whereas range-list partitioning is commonly used for a discrete list values within a range partition. List-range partitioning is less commonly used to store historical data, even though equivalent scenarios are all suitable. Range-list partitioning can be implemented using interval-list partitioning, whereas list-range partitioning does not support interval partitioning.
Example 3-13 shows a donations table that stores donations in different currencies. The donations are categorized into small, medium, and high, depending on the amount. Due to currency differences, the ranges are different.
Example 3-13 Creating a table with composite list-range partitioning
CREATE TABLE donations
( id NUMBER
, name VARCHAR2(60)
, beneficiary VARCHAR2(80)
, payment_method VARCHAR2(30)
, currency VARCHAR2(3)
, amount NUMBER
) PARTITION BY LIST (currency)
SUBPARTITION BY RANGE (amount)
( PARTITION p_eur VALUES ('EUR')
( SUBPARTITION p_eur_small VALUES LESS THAN (8)
, SUBPARTITION p_eur_medium VALUES LESS THAN (80)
, SUBPARTITION p_eur_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_gbp VALUES ('GBP')
( SUBPARTITION p_gbp_small VALUES LESS THAN (5)
, SUBPARTITION p_gbp_medium VALUES LESS THAN (50)
, SUBPARTITION p_gbp_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_aud_nzd_chf VALUES ('AUD','NZD','CHF')
( SUBPARTITION p_aud_nzd_chf_small VALUES LESS THAN (12)
, SUBPARTITION p_aud_nzd_chf_medium VALUES LESS THAN (120)
, SUBPARTITION p_aud_nzd_chf_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_jpy VALUES ('JPY')
( SUBPARTITION p_jpy_small VALUES LESS THAN (1200)
, SUBPARTITION p_jpy_medium VALUES LESS THAN (12000)
, SUBPARTITION p_jpy_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_inr VALUES ('INR')
( SUBPARTITION p_inr_small VALUES LESS THAN (400)
, SUBPARTITION p_inr_medium VALUES LESS THAN (4000)
, SUBPARTITION p_inr_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_zar VALUES ('ZAR')
( SUBPARTITION p_zar_small VALUES LESS THAN (70)
, SUBPARTITION p_zar_medium VALUES LESS THAN (700)
, SUBPARTITION p_zar_high VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_default VALUES (DEFAULT)
( SUBPARTITION p_default_small VALUES LESS THAN (10)
, SUBPARTITION p_default_medium VALUES LESS THAN (100)
, SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE)
)
) ENABLE ROW MOVEMENT;