All Products
Search

This Product

    Data Management:Query a logical table

    Document Center

    Data Management:Query a logical table

    Last Updated:Mar 28, 2026

    Use a logical table to query, write, and aggregate data spread across sharded physical databases and tables — without routing to each shard manually.

    How it works

    When a database is horizontally sharded, the same table structure is split across multiple physical tables. For example, an orders table split into 10 shards becomes t_order_0 through t_order_9 in the underlying physical databases.

    Data Management (DMS) introduces a two-level abstraction to handle this:

    LevelNameDescription
    Database levelLogical databaseA unified view of all sharded physical databases
    Table levelLogical tableA unified view of all physical tables with the same structure, for example, t_order

    Queries run against the logical table. DMS routes them to the correct physical tables automatically.

    SQL support

    The following table summarizes SQL statement support based on whether a routing algorithm is configured.

    SQL statementWithout routing algorithmWith routing algorithm
    SELECTSupportedSupported
    UPDATESupportedSupported
    DELETESupportedSupported
    INSERTNot supportedSupported

    With a routing algorithm configured, the following SQL features work across all physical shards:

    • Aggregation: COUNT() and other aggregate functions

    • Grouping: GROUP BY

    • Other SQL syntax follows the source database conventions

    Limitation: Subqueries are not supported.

    Prerequisites

    Before you begin, ensure that you have:

    • A logical database with physical databases and tables already configured in DMS

    • Access to the DMS console with sufficient permissions to run SQL statements

    Query a logical table

    Step 1: Select a logical database and run a query

    1. In the DMS console, select the logical database.

    2. Run SELECT, UPDATE, or DELETE statements as needed. At this stage, INSERT is not supported because no routing algorithm is configured yet.

    Step 2: Configure a routing algorithm

    A routing algorithm determines which physical shard receives write operations, based on the value of a specified field.

    Configure a modulo routing algorithm to get started. For more complex routing logic, see Configure a routing algorithm.

    Step 3: Insert data into the logical table

    After the routing algorithm is configured, run an INSERT statement to write data into the logical table. DMS routes the row to the correct physical shard automatically.

    Step 4: Query data using routing-based fields

    With a routing algorithm in place, queries on routing-based fields go directly to the target physical table — no manual shard switching required.

    Query results include the physical database and table name in the last column. Click the value in that column to open the physical table and run the same query directly against it.

    To query across all shards without a routing-based field — for example, to count all records — run a query without shard-key conditions:

    SELECT COUNT(*) FROM logical_table_name;
    Note

    GROUP BY and other standard SQL clauses work the same way as in the source database. Subqueries are not supported.

    What's next