This topic describes how to use the RANGE_HASH function.

Scenarios

The RANGE_HASH function is applicable to scenarios where two partitioning keys are required but only one partitioning key value is available during queries.

Limits

  • The two partitioning keys must be of the same data type, which can be the string or number type.
  • The two partitioning keys cannot be modified.
  • The partitioning keys do not support range query.
  • When data is inserted, the last N characters of the two partitioning keys must be the same.
  • A string must contain no less than N characters.
  • The version of the PolarDB-X 1.0 instance must be 5.1.28-1320920 or later.

Routing method

Calculate the hash value based on the last N characters of either partitioning key, then divide by the number of database shards and find the remainder. The letter N indicates the third parameter in the function. For example, during calculation of the RANGE_HASH(COL1, COL2, N) function, COL1 is preferentially selected, and then its last N characters are truncated for calculation. If COL1 does not exist, COL2 is selected and truncated for calculation.

Examples

Assume that PolarDB-X 1.0 has eight physical database shards. You want to partition data into database shards by buyer ID and order ID. However, only one of these IDs is available during queries. You can use the following Data Definition Language (DDL) statement to create order tables: 

create table test_order_tb (  
    id int, 
    buyer_id varchar(30) DEFAULT NULL,  
    order_id varchar(30) DEFAULT NULL, 
    create_time datetime DEFAULT NULL,
    primary key(id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
    dbpartition by RANGE_HASH(buyer_id,order_id, 10) 
    tbpartition by RANGE_HASH (buyer_id,order_id, 10) tbpartitions 3;