All Products
Search

This Product

    PolarDB:SET PARTITION_HINT

    Document Center

    PolarDB:SET PARTITION_HINT

    Last Updated:Mar 28, 2026

    Routes subsequent SQL statements in the current session to a specific shard on a data node, bypassing the default automatic routing logic.

    Syntax

    SET PARTITION_HINT = [PARTITION_NAME | GROUP_NAME | GROUP_NAME:TABLE_INDEX]

    To clear the hint and resume querying all shards, set an empty string:

    SET PARTITION_HINT = '';

    Parameters

    ParameterDescription
    PARTITION_NAMEThe partition_name of the target partition in a database in AUTO mode.
    GROUP_NAMEThe GROUP_NAME of the target group in a database in DRDS mode.
    GROUP_NAME:TABLE_INDEXThe group name and the zero-based index of the table within that group, for databases in DRDS mode where a group contains multiple tables.

    Usage notes

    • Version requirement: Supported only on cluster of PolarDB-X 5.4.16-16773973.

    • Mode support: Supported in databases in AUTO mode and DRDS mode.

    Use cases

    • Debug data distribution: Query a single shard directly to verify which rows are stored on it.

    • Isolate shard performance: Run queries against one shard at a time to diagnose load imbalance.

    • Validate shard-level results: Confirm that a specific partition or group contains the expected data after a write.

    Examples

    The following examples use a table multi_db_single_tbl distributed across three shards, with one row on each shard: (100001, 'a'), (100002, 'b'), and (100003, 'c').

    Query a single shard

    Set the hint to route to PARTITION_HINT_TEST_DRDS_000003_GROUP, table index 0:

    SET PARTITION_HINT = 'PARTITION_HINT_TEST_DRDS_000003_GROUP:0';
    Query OK, 0 rows affected (0.00 sec)

    Run a query. Only the row stored on that shard is returned:

    SELECT * FROM multi_db_single_tbl;
    +--------+------+
| id     | name |
+--------+------+
| 100003 | c    |
+--------+------+
1 row in set (0.02 sec)

    Query all shards

    Clear the hint to restore default routing across all shards:

    SET PARTITION_HINT = '';
    Query OK, 0 rows affected (0.00 sec)

    Run the same query. All three rows are returned:

    SELECT * FROM multi_db_single_tbl;
    +--------+------+
| id     | name |
+--------+------+
| 100003 | c    |
+--------+------+
| 100002 | b    |
+--------+------+
| 100001 | a    |
+--------+------+
3 rows in set (0.04 sec)