Scan all or specific table shards in all or specific database shards - PolarDB

This topic describes the hint syntax that is used to scan all or specific table shards in all or specific database shards. This topic also provides examples.

PolarDB-X provides the capability to route an SQL statement to one or more database shards for execution. PolarDB-X also provides SCAN hints to scan all or specific table shards in all or specific database shards. You can use a SCAN hint to route an SQL statement to all database shards at a time. For example, you can query all the table shards in a specified database shard or query the amount of data in each physical table that corresponds to a specified logical table.

You can use SCAN hints to execute SQL statements in the following manners:

Execute an SQL statement on all table shards in all database shards.
Execute an SQL statement on all table shards in the specified database shards.
Execute an SQL statement on the specified table shards in the specified database shards. The table shards are specified by calculating the names of the physical tables based on given conditions.
Execute an SQL statement on the specified table shards in the specified database shards. The table shards are specified by using the names of physical tables.

SCAN hints can be used in DML statements, DDL statements, and some Data Access Language (DAL) statements.

Syntax

# SCAN HINT

# Route an SQL statement to all table shards in all database shards.
SCAN()                               

# Route an SQL statement to all table shards in the specified database shards.
SCAN(NODE="node_list")               # Specify the database shards.

# Route an SQL statement to the specified table shards in the specified database shards. The table shards are specified by calculating the names of physical tables based on given conditions.
SCAN(
  [TABLE=]"table_name_list"          # Specify the names of logical tables.
  , CONDITION="condition_string"     # Calculate the names of physical tables based on the values of the TABLE and CONDITION parameters.
  [, NODE="node_list"] )             # Filter the results that are calculated based on the value of the CONDITION parameter to retain only the names of the table shards that exist in the specified database shards.

# Route an SQL statement to the specified table shards in the specified database shards. The table shards are specified by using the names of physical tables.
SCAN(
  [TABLE=]"table_name_list"          # Specify the names of logical tables.
  , REAL_TABLE=("table_name_list")   # Specify the names of physical tables. These physical table names are used to query data from all the specified database shards.
  [, NODE="node_list"] )             # Filter the results that are calculated based on the value of the CONDITION parameter to retain only the names of the table shards that exist in the specified database shards.


# Specify the names of physical tables or logical tables.
table_name_list: 
    table_name [, table_name]...

# Specify database shards by using the group_key or group_index parameter. You can execute the SHOW NODE statement to obtain the group key and group index of each shard of a database. 
node_list: 
    {group_key | group_index} [, {group_key | group_index}]...
    
# Specify an SQL WHERE clause. You must specify conditions for each table, such as t1.id = 2 and t2.id = 2.
condition_string: 
    where_condition

Additional considerations

PolarDB-X allows you to customize a hint by using one of the following formats: /*+TDDL:hint_command*/ and /!+TDDL:hint_command*/.
In the official MySQL command-line client, if you execute SQL statements that contain PolarDB-X custom hints in the /*+TDDL:hint_command*/ format, add the -c parameter in the command that is used to log on to the client. If you do not add the -c parameter, the client deletes MySQL comments in SQL statements before it sends the SQL statements to servers for execution. The PolarDB-X custom hints that use the /*+TDDL:hint_command*/ format are defined as MySQL comments. As a result, the PolarDB-X custom hints are deleted and cannot take effect. For more information, see mysql client options.

Examples

Execute an SQL statement on all table shards in all database shards.
```
SELECT /*+TDDL:scan()*/ COUNT(1) FROM t1      
```
PolarDB-X routes the SQL statement to all the physical tables of the t1 logical table. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result.
Execute an SQL statement on all table shards in the specified database shards.
```
SELECT /*+TDDL:scan(node='0,1,2')*/ COUNT(1) FROM t1          
```
PolarDB-X first calculates the names of physical tables of the t1 logical table in the 0000, 0001, and 0002 database shards. Then, PolarDB-X routes the SQL statement to the specified table shards. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result.
Execute an SQL statement on the specified table shards based on given conditions.
```
SELECT /*+TDDL:scan('t1', condition='t1.id = 2')*/ COUNT(1) FROM t1           
```
PolarDB-X first calculates the names of all physical tables that correspond to the t1 logical table and that meet the specified conditions. Then, PolarDB-X routes the SQL statement to the specified table shards. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result.
Execute an SQL statement that contains a JOIN clause on the specified table shards based on given conditions.
```
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"
            
```
PolarDB-X first calculates the names of physical tables that correspond to the t1 and t2 logical tables and that meet the specified conditions. Then, PolarDB-X routes the SQL statement to the specified table shards. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result. Before you use this custom hint, make sure that data from the t1 and t2 logical tables is partitioned into the same number of table shards in the same number of database shards. Otherwise, the database shards that are calculated by PolarDB-X can be different, and an error is returned.
Execute an SQL statement on the specified table shards in the specified database shards. The table shards are specified by using the names of physical tables.
```
SELECT /*+TDDL:scan('t1', real_table=("t1_00", "t1_01"))*/ COUNT(1) FROM t1
            
```
PolarDB-X routes the SQL statement to the t1_00 and t1_01 table shards in all database shards. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result.
Execute an SQL statement that contains a JOIN clause on the specified table shards in the specified database shards. The table shards are specified by using the names of 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";
            
```
PolarDB-X routes the SQL statement to the t1_00, t2_00, t1_01, and t2_01 table shards in all database shards. After the SQL statement is executed, PolarDB-X merges the result sets and returns the final result.