Sharding Advisor Output Tables
To review the sharding configurations and related information, you can query the following output database tables, which are stored in the same schema as your source database.
SHARDINGADVISOR_CONFIGURATIONS Table
Each row of the SHARDINGADVISOR_CONFIGURATIONS table represents a
table in a ranked sharded configuration, and provides information about whether to shard
or duplicate it, and if sharded, its level in a table family hierarchy, its parent
table, root table sharding key, foreign key reference constraints, and table size per
shard.
SHARDINGADVISOR_CONFIGURATIONS Schema
| Column | Description |
|---|---|
RANK |
The rank of the sharding configuration based on the ranking algorithm |
TABLENAME |
Name of the table in the sharding configuration |
TABLETYPE |
‘S’ (Sharded), ‘D’ (Duplicated), or ‘L’ (Local) |
TABLELEVEL |
Level of the table in the table family hierarchy, NULL for duplicated tables |
PARENT |
Parent of the table in the table family hierarchy, NULL for duplicated tables |
SHARDBY |
Sharding method. REFERENCE for
sharding by reference, or PARENT for sharding by
PARENT clause, for child tables.
|
SHARDINGORREFERENCECOLS |
Sharding key for the root table, partition by
REFERENCE or PARENT for the
child tables in a table family, and NULL for duplicated
tables
|
UNENFORCEABLECONSTRAINTS |
Foreign key constraints other than the reference columns, which cannot be enforced |
SIZEOFTABLE |
Size of the table per shard |
SHARDINGADVISOR_CONFIGDETAILS Table
Each row of the SHARDINGADVISOR_CONFIGDETAILS table represents a
ranked sharding configuration, and provides the number and collective size, per shard,
of each type of table, the number of each type of query, and based on your source
database's current workload, an estimated cost.
SHARDINGADVISOR_CONFIGDETAILS Schema
| Column | Description |
|---|---|
RANK |
The rank of the sharding configuration based on the ranking algorithm |
CHOSENBYUSER |
‘Y’ if the sharding configuration is chosen by the user, NULL for other sharding configurations |
NUMSHARDEDTABLES |
Number of sharded tables in this sharding configuration |
SIZEOFSHARDEDTABLES |
Cumulative size of sharded tables (per shard) in this sharding configuration |
NUMDUPLICATEDTABLES |
Number of duplicated tables in this sharding configuration |
SIZEOFDUPLICATEDTABLES |
Cumulative size of duplicated tables (per shard) in this sharding configuration |
NUMSINGLESHARDQUERIES |
Number of single shard queries in the query workload for this sharding configuration |
NUMMULTISHARDQUERIES |
Number of multi-shard queries in the query workload for this sharding configuration |
NUMCROSSSHARDQUERIES |
Number of multi-shard queries that require an external join in the query workload for this sharding configuration |
COST |
Cost of the sharding configuration based on the costing algorithm |
SHARDINGADVISOR_QUERYTYPES Table
Each row of the SHARDINGADVISOR_QUERYTYPES table represents a query
in the workload, and lists the query type and SQL ID. Note that the same query can be of
a different query type depending on the sharding configuration.
SHARDINGADVISOR_QUERYTYPES Schema
| Column | Description |
|---|---|
RANK |
The rank of the sharding configuration based on the ranking algorithm |
SQLID |
The query SQL ID |
QUERYTYPE |
The type of the query in this sharding
configuration: SINGLE SHARD QUERY, MULTI
SHARD QUERY, or CROSS SHARD
QUERY |