Operators

LogicalView

LogicalView reads data from data nodes. It is the primary pushdown operator for SELECT statements and can push down a broader set of operations than the TableScan and IndexScan operators — including Project, Filter, aggregate operators, sort operators, join operators, and subqueries.

An execution plan always shows the SQL template that runs on the storage layer alongside the list of table shards it targets.

When the optimizer uses it: Whenever a SELECT statement targets one or more shards, LogicalView appears in the plan.

Example

EXPLAIN SELECT * FROM sbtest1 WHERE id > 1000;

Output:

Gather(concurrent=true)
  LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")

Arguments

LogicalModifyView

LogicalModifyView writes data to data nodes. It covers INSERT, UPDATE, and DELETE statements and contains the same fields as LogicalView: physical table shard names, shard count, and an SQL template.

When the execution plan cache is enabled, constants in the SQL template are replaced with ? placeholders.

When the optimizer uses it: Whenever a write statement (INSERT, UPDATE, or DELETE) targets one or more shards, LogicalModifyView appears in the plan.

Examples

EXPLAIN UPDATE sbtest1 SET c='Hello, DRDS' WHERE id > 1000;

Output:

LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="UPDATE `sbtest1` SET `c` = ? WHERE (`id` > ?)")

EXPLAIN DELETE FROM sbtest1 WHERE id > 1000;

Output:

LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="DELETE FROM `sbtest1` WHERE (`id` > ?)")

PhyTableOperation

PhyTableOperation operates directly on a single physical table shard. It is used primarily for INSERT statements. When a SELECT is routed to a table shard, PhyTableOperation executes it.

In a multi-row INSERT, each row gets its own PhyTableOperation.

When the optimizer uses it: For INSERT statements — each row is assigned to exactly one physical shard. Also used when a SELECT statement is routed to a table shard.

Example

EXPLAIN INSERT INTO sbtest1 VALUES(1, 1, '1', '1'),(2, 2, '2', '2');

Output:

PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_001]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_001`,1,1,1,1")
PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_002]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_002`,2,2,2,2")

Two rows in the INSERT produce two PhyTableOperation entries, one per shard.

Arguments

IndexScan

IndexScan reads data from data nodes using a global secondary index (GSI) rather than the base table. Its behavior is identical to LogicalView except it scans an index table instead of a base table.

When the optimizer uses it: When a query predicate matches the partition key of a GSI column, the optimizer uses IndexScan to target only the relevant index shard, avoiding a full scan of the base table. If no GSI exists on the predicate column, or if the predicate column is not a partition key, LogicalView scans all base table shards instead.

Example

EXPLAIN SELECT * FROM sequence_one_base WHERE integer_test=1;

Output:

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 IndexScan(tables="DRDS_POLARX1_QATEST_APP_000000_GROUP.gsi_sequence_one_index_3a0A_01", sql="SELECT `pk`, `integer_test`, `varchar_test`, `char_test`, `blob_test`, `tinyint_test`, `tinyint_1bit_test`, `smallint_test`, `mediumint_test`, `bit_test`, `bigint_test`, `float_test`, `double_test`, `decimal_test`, `date_test`, `time_test`, `datetime_test`, `timestamp_test`, `year_test`, `mediumtext_test` FROM `gsi_dml_sequence_one_index_index1` AS `gsi_dml_sequence_one_index_index1` WHERE (`integer_test` = ?)")

In this example, sequence_one_base has a GSI named gsi_sequence_one_index on the integer_test column. Because integer_test=1 matches the GSI partition key, only one index shard is scanned. Without the GSI, or if integer_test were not a partition key, all shards of the base table would be scanned.

Gather

Gather merges results from multiple table shards into a single result set. It appears as the parent of LogicalView in most execution plans, collecting data from all scanned shards.

When the optimizer uses it: Whenever LogicalView scans multiple shards and the compute node needs a unified result set, Gather appears above LogicalView in the plan tree.

Exchange

Exchange is a logical operator that redistributes data between nodes without performing any computation. It feeds redistributed data to downstream operators. Three redistribution strategies are used:

When the optimizer uses it: Exchange appears in execution plans where data must be redistributed across nodes before a join or aggregation can proceed. The redistribution strategy chosen depends on the downstream operator: hash distribution for joins and aggregations, broadcast for MPP execution plans, singleton when a single stream is needed.

MergeSort

MergeSort merges multiple sorted data streams from different shards into a single sorted stream. The optimizer uses it when an ORDER BY query spans multiple shards — each shard sorts locally, and MergeSort merges the results at the compute node.

When the optimizer uses it: When an ORDER BY clause cannot be satisfied by a single shard, each shard sorts its rows locally (visible as ORDER BY in the LogicalView SQL template), and MergeSort performs the final merge on the compute node. It often appears alongside LIMIT for efficient top-N queries.

Example

EXPLAIN SELECT * FROM sbtest1 WHERE id > 1000 ORDER BY id LIMIT 5,10;

Output:

MergeSort(sort="id ASC", offset=?1, fetch=?2)
  LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `id` LIMIT (? + ?)")

Arguments

UnionAll and UnionDistinct

UnionAll and UnionDistinct merge two or more result sets into one. UnionAll corresponds to UNION ALL; UnionDistinct corresponds to UNION DISTINCT and removes duplicate rows. These operators can be executed on either compute nodes or data nodes, depending on what the optimizer determines.

Example

EXPLAIN SELECT * FROM sbtest1 WHERE id > 1000
UNION DISTINCT
SELECT * FROM sbtest1 WHERE id < 200;

Output:

UnionDistinct(concurrent=true)
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` < ?)")

Project

Project selects columns from the input rows, evaluates expressions, and outputs the result. It can compute arithmetic expressions, call functions, or return constants.

When the optimizer uses it: Project appears whenever the output columns differ from the input columns — for example, when a query selects specific columns, computes derived values, or applies functions. Predicates that can be evaluated at the storage layer are pushed into LogicalView instead.

Example

EXPLAIN SELECT 'Hello, DRDS', 1 / 2, CURTIME();

Output:

Project(Hello, DRDS="_UTF-16'Hello, DRDS'", 1 / 2="1 / 2", CURTIME()="CURTIME()")

The plan lists each output column alongside its source value, expression, or function.

Filter

Filter evaluates a predicate and passes only the rows that satisfy it. The condition shown in the plan is the predicate applied at the compute node. Predicates that can be evaluated at the storage layer are pushed down into LogicalView instead.

When the optimizer uses it: Filter appears when a predicate cannot be pushed to the storage layer — typically when it depends on an aggregated or computed value (such as a HAVING clause), or when the predicate references columns that are only available after a join or aggregation at the compute node.

Example

EXPLAIN SELECT k, AVG(id) avg_id FROM sbtest1 WHERE id > 1000 GROUP BY k HAVING avg_id > 1300;

Output:

Filter(condition="avg_id > ?1")
  Project(k="k", avg_id="sum_pushed_sum / sum_pushed_count")
    SortAgg(group="k", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
      MergeSort(sort="k ASC")
        LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, SUM(`id`) AS `pushed_sum`, COUNT(`id`) AS `pushed_count` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`")

The WHERE id > 1000 condition does not appear in Filter because it is pushed down into LogicalView — visible as WHERE (id > ?) in the SQL template. Only the HAVING avg_id > 1300 condition, which depends on the aggregated result, remains in Filter.