Use the Scan hint to query specific table and database shards - PolarDB - Alibaba Cloud - PolarDB

SCAN HINT routes SQL statements to all or a subset of physical table shards across all or selected database shards, then merges the results. Use it when you need to bypass PolarDB-X 1.0's default routing and target specific physical tables directly.

Typical use cases:

Count the rows in each physical table that maps to a given logical table
Query all table shards in a specific database shard

SCAN HINT applies to PolarDB-X 1.0 V5.3 and later. It works with DML statements, DDL statements, and some Data Access Language (DAL) statements.

This topic applies to PolarDB-X 1.0 5.3 and later.

Syntax

-- Scan all table shards in all database shards
SCAN()

-- Scan all table shards in specified database shards
SCAN(NODE="node_list")

-- Scan table shards calculated from conditions
SCAN(
  [TABLE=]"table_name_list"       -- Logical table names
  , CONDITION="condition_string"  -- Calculates physical table names from TABLE and CONDITION values
  [, NODE="node_list"] )          -- Filters results to only the specified physical databases

-- Scan explicitly specified physical table shards
SCAN(
  [TABLE=]"table_name_list"          -- Logical table names
  , REAL_TABLE=("table_name_list")   -- Physical table names, applied across all physical databases
  [, NODE="node_list"] )             -- Filters results to only the specified physical databases

Parameters

Parameter	Description
`NODE="node_list"`	Specifies the database shards to target. Use `GROUP_KEY` or `GROUP_INDEX` values. Run `SHOW NODE` to get these values. Format: `{group_key \| group_index} [, {group_key \| group_index}]...`
`[TABLE=]"table_name_list"`	Specifies logical table names. Format: `table_name [, table_name]...`
`CONDITION="condition_string"`	An SQL WHERE clause that filters which physical tables to target. Specify conditions for each table, for example: `t1.id = 2 and t2.id = 2`.
`REAL_TABLE=("table_name_list")`	Explicitly specifies physical table names. These tables are queried across all physical databases.

Choosing a scan mode

Mode	When to use
`SCAN()`	Scan every physical shard — broadest scope
`SCAN(NODE=...)`	Limit to specific database shards when you know the target nodes
`SCAN(TABLE=..., CONDITION=...)`	Let PolarDB-X 1.0 calculate which physical tables match your conditions
`SCAN(TABLE=..., REAL_TABLE=...)`	Explicitly name the physical tables — requires knowing exact table names

Usage notes

PolarDB-X 1.0 hints support two formats:

/*+TDDL:hint_command*/
/!+TDDL:hint_command*/

When using the /*+TDDL:hint_command*/ format with the MySQL command-line client, add the -c flag when connecting:

mysql -c -h <host> -P <port> -u <username> -p

Without -c, the MySQL client strips comments from SQL statements before sending them to the server. PolarDB-X 1.0 hints in the /*+TDDL:...*/ format are defined as MySQL comments, so the hints are deleted and cannot take effect.

PolarDB-X 1.0 hints in the /*+TDDL:...*/ format are treated as MySQL comments. For more information, see mysql client options.

Examples

Scan all table shards in all database shards

SELECT /*+TDDL:scan()*/ COUNT(1) FROM t1

DRDS routes the statement to all physical tables of logical table t1, merges the result sets, and returns the final count.

Scan all table shards in specified database shards

SELECT /*+TDDL:scan(node='0,1,2')*/ COUNT(1) FROM t1

DRDS calculates the physical table names for logical table t1 in database shards 0000, 0001, and 0002, routes the statement to those shards, merges the result sets, and returns the final count.

Scan table shards calculated from conditions

SELECT /*+TDDL:scan('t1', condition='t1.id = 2')*/ COUNT(1) FROM t1

DRDS calculates which physical tables of logical table t1 match the condition, routes the statement to those shards, merges the result sets, and returns the final count.

For JOIN queries across multiple tables:

SELECT /*+TDDL:scan('t1, t2', condition='t1.id = 2 and t2.id = 2')*/ * FROM t1 a JOIN t2 b ON a.id = b.id WHERE b.name = "test"

DRDS calculates the physical tables for t1 and t2 that match their respective conditions, routes the statement, and merges the results.

Important

When using CONDITION with a JOIN, both tables must belong to the same database shard and have the same shard count. If these conditions are not met, PolarDB-X 1.0 resolves table shards from different database shards, and DRDS reports an error.

Scan explicitly specified physical table shards

SELECT /*+TDDL:scan('t1', real_table=("t1_00", "t1_01"))*/ COUNT(1) FROM t1

DRDS routes the statement to physical table shards t1_00 and t1_01 across all database shards, merges the result sets, and returns the final count.

For JOIN queries with explicitly specified physical tables:

SELECT /*+TDDL:scan('t1, t2', real_table=("t1_00,t2_00", "t1_01,t2_01"))*/ * FROM t1 a JOIN t2 b ON a.id = b.id WHERE b.name = "test";

DRDS routes the statement to t1_00, t2_00, t1_01, and t2_01 across all database shards, merges the result sets, and returns the final result.