Range-hash partitioning uses range partitioning and hash subpartitioning.
Range-hash partitioning is common for large tables that store historical data and often used to join with other large tables. Typical examples of such large tables are data warehouse tables. Range-hash partitioning combines the advantages of partition pruning at the range level and the opportunity to perform parallel join operations on a partition or parts of a partition at the hash level. Some particular SQL statements can benefit from partition pruning in two dimensions.
Range-hash partitioning can also be used in hash partitioned tables. However, tumbling windows are required in this case. Data can be moved from one storage layer to another for compression, stored in read-only tables, and eventually cleared. Range partitioning is typically used in information lifecycle management scenarios to implement tiered storage.
In the following example,range-hash partitioning is used for the TPCH orders table:
CREATE TABLE `orders` (
`o_orderkey` int(11) NOT NULL,
`o_custkey` int(11) NOT NULL,
`o_orderstatus` char(1) DEFAULT NULL,
`o_totalprice` decimal(10,2) DEFAULT NULL,
`o_orderdate` date NOT NULL,
`o_orderpriority` char(15) DEFAULT NULL,
`o_clerk` char(15) DEFAULT NULL,
`o_shippriority` int(11) DEFAULT NULL,
`o_comment` varchar(79) DEFAULT NULL,
PRIMARY KEY (`o_orderkey`,`o_orderdate`,`o_custkey`),
KEY `o_orderkey` (`o_orderkey`),
KEY `i_o_custkey` (`o_custkey`),
KEY `i_o_orderdate` (`o_orderdate`)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(o_orderdate)
SUBPARTITION BY HASH (`o_custkey`)
SUBPARTITIONS 64
(PARTITION item1 VALUES LESS THAN ('1992-01-01'),
PARTITION item2 VALUES LESS THAN ('1993-01-01'),
PARTITION item3 VALUES LESS THAN ('1994-01-01'),
PARTITION item4 VALUES LESS THAN ('1995-01-01'),
PARTITION item5 VALUES LESS THAN ('1996-01-01'),
PARTITION item6 VALUES LESS THAN ('1997-01-01'),
PARTITION item7 VALUES LESS THAN ('1998-01-01'),
PARTITION item8 VALUES LESS THAN ('1999-01-01'),
PARTITION item9 VALUES LESS THAN (MAXVALUE));