When to Use Hash Partitioning

Hash partitioning is useful for randomly distributing data between partitions based on hash algorithms rather than grouping similar data.

In some cases, it is not obvious which partition the partition data should be located in, although the partition key can be determined. In some cases, you don't want to group similar data like range partitioning, but want the distribution of data to be inconsistent with its business or logical view. Hash partitioning puts a row into a partition based on the result of passing the keyword to the hash algorithm.

With this approach, data is distributed randomly across partitions rather than grouped. This is a good approach for some data, but it may not be appropriate for managing historical data. However, hash partitioning shares some of the same performance characteristics as range partitioning. For example, partitioning pruning is limited to equality predicates. You can also use partitioned joins, parallel index access, and parallel DML.

The advantage of hash partitioning is that the distribution of data is almost random, so the distribution is relatively uniform, which can avoid hot spot problems to some extent.

The disadvantages of hash partitioning are as follows:

• You cannot perform a range query without storing additional data.
• Since each node requires a corresponding hash value when nodes are added or deleted, the addition of nodes requires modification of the hash function, which causes much of the existing data to be remapped, causing massive data movement. In the meantime, the system may not continue to work.

Example 1 Four hash partitions are created for table Sales_hash using the s_productid column as the partitioning keyword. Parallel joins with the products table can take advantage of partial or full partitioned joins. At this time, partitioning pruning is conducive to the query that only accesses the sales data of a single product or part of the product.

If you specify a hash partition number instead of an explicit partition name, MogDB automatically generates an internal name for the partition.

Example 1 Creating a hash-partitioned table

CREATE TABLE sales_hash
  (s_productid  NUMBER,
   s_saledate   DATE,
   s_custid     NUMBER,
   s_totalprice NUMBER)
PARTITION BY HASH(s_productid)
( PARTITION p1 , 
  PARTITION p2, 
  PARTITION p3 , 
  PARTITION p4 
);