When order data is split into 1,024 physical tables spread across 32 databases, querying across all shards requires shard-aware SQL and per-database permission management. A logical table in Data Management (DMS) eliminates this overhead: it presents all physical tables that share the same schema as a single unified table, so you can query, export, and change data without managing each physical shard individually.
For example, if order data is distributed across tables named order_0000 through order_1023 in 32 databases, DMS exposes them as a single logical table called order. SQL operations in the SQL Console target the logical table; DMS routes them to the correct physical tables automatically.
Use cases
Logical tables support three common sharding architectures:
Single database with table sharding — One physical database contains multiple table partitions. Use a logical table to query across all partitions without writing shard-aware SQL.
Database sharding with table sharding (most common) — Data is distributed across multiple databases, each containing multiple table partitions. Physical tables are evenly distributed across databases and share the same suffix numbering. For example, each database holds tables numbered 1 through 12.
Database sharding without table sharding — Data is spread across multiple database shards, but each shard has one physical table with the same name. A logical table unifies them under a single identifier.
Logical table concepts
Logical table and physical table
A logical table is the logical name you use in SQL. The corresponding physical tables are the actual tables stored in each database shard.
| Concept | Example |
|---|---|
| Logical table name | order |
| Physical table names | order_0000, order_0001, …, order_1023 |
If the number of physical tables is greater than one, the tables are evenly divided among the logical databases. Physical table names follow this naming convention:
The suffix
_xxxxis a four-digit number starting from0000and incrementing by 1.In most cases, the total number of physical tables is a power of 2.
The total number of physical tables must be evenly divisible by the number of databases. For example, 1,024 tables across 32 databases gives 32 physical tables per database. If 1,024 tables cannot be evenly divided across 33 databases, DMS cannot create the logical table.
Schema consistency
All physical tables mapped to the same logical table must have identical schemas — the same column names and column types. If schemas diverge, DMS blocks the aggregation and alerts you to the inconsistency.
Operational scope
Perform all operations on a logical table within its corresponding logical database.
A logical table can also be used as a single table.
Applying for permissions on a logical database grants access to all physical databases associated with it. You do not need to request permissions for each physical database separately.
Configure a logical table
DMS supports two methods for configuring logical tables. Use automatic configuration unless you need to manually reprocess an existing logical database.
| Method | When to use | How it works |
|---|---|---|
| Automatic | After configuring a logical database for the first time | DMS detects physical table schemas and creates logical tables automatically. Each physical table maps to exactly one logical table. |
| Manual (Re-extract) | When a logical table is missing or needs to be refreshed | Trigger re-extraction on demand from the Logical Database result list. |
Automatic configuration
After you configure a logical database, DMS automatically creates logical tables based on the schemas of the physical tables. Two rules apply:
Each physical table can belong to only one logical table.
All physical tables for the same logical table must have identical schemas. Tables with schema mismatches are not aggregated.
Avoid changing the logical database configuration after it is set up unless necessary.
Manual configuration (Re-extract)
To refresh a logical table manually:
In the Logical Database result list, find the logical database you want to manage.
In the Actions column, hover over More.
Click Re-extract Table.
FAQ
A logical table exists, but I cannot find it in the table list of the logical database. What should I do?
The table list may be out of sync. Use Global Search to locate the logical database and re-extract its tables:
In the top navigation bar of the Data Management console, click the
icon to open the Global Search page.Find the logical database.
In the Actions column, hover over More, then click Re-extract Table.