Hash partitioning can be used for data which is not distributed in any rules or does not fall within a search range. You can use hash partitioning to distribute the data in partition key columns to different partitions based on the hash algorithm.
Hash partitioning serves the following purposes:
Evenly distribute data between partitions for parallel access to data in partitions.
Use partition pruning to minimize the overheads of partition key-based equality queries.
Randomly distribute data to avoid I/O bottlenecks.
Partition keys must meet the following requirements:
Select a unique or almost unique column or a combination of columns.
Create multiple partitions and subpartitions for each partition when the number of partitions is a power of 2, such as when the number of partitions is 2, 4, 8, 16, 32, 64, and 128.
Example: Use c_custkey as the partition key to create four hash partitions for the customer table.
CREATE TABLE `customer` (
`c_custkey` int(11) NOT NULL,
`c_name` varchar(25) DEFAULT NULL,
`c_address` varchar(40) DEFAULT NULL,
`c_nationkey` int(11) DEFAULT NULL,
`c_phone` char(15) DEFAULT NULL,
`c_acctbal` decimal(10,2) DEFAULT NULL,
`c_mktsegment` char(10) DEFAULT NULL,
`c_comment` varchar(117) DEFAULT NULL,
PRIMARY KEY (`c_custkey`),
KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH(c_custkey)
( PARTITION p1,
PARTITION p2,
PARTITION p3,
PARTITION p4
);After you specify the number of hash partitions, the internal names of the partitions are automatically generated. Example:
CREATE TABLE `customer` (
`c_custkey` int(11) NOT NULL,
`c_name` varchar(25) DEFAULT NULL,
`c_address` varchar(40) DEFAULT NULL,
`c_nationkey` int(11) DEFAULT NULL,
`c_phone` char(15) DEFAULT NULL,
`c_acctbal` decimal(10,2) DEFAULT NULL,
`c_mktsegment` char(10) DEFAULT NULL,
`c_comment` varchar(117) DEFAULT NULL,
PRIMARY KEY (`c_custkey`),
KEY `i_c_nationkey` (`c_nationkey`)
) ENGINE=InnoDB
PARTITION BY HASH (`c_custkey`)
PARTITIONS 64;