Query Processing for Single-Shard Queries
A single-shard query is a query which needs to scan data from only one shard and does not need to lookup data from any other shards.
The single-shard query is similar to a client connecting to a specific shard and issuing a query on that shard. In this scenario, the entire query will be processed on the single participating shard, and the coordinator just passes processed rows back to the client. The plan on the coordinator is similar to the remote mapped cursor.
For example, the following query is fully mapped to a single shard because the data for customer 123 is located only on that shard.
SELECT count(*) FROM customers c, orders o WHERE c.custno = o.custno and c.custno = 123;
The query contains a condition on the shard key that maps to one and only one shard which is known at query compilation time (literals) or query start time (bind). The query is fully processed on the qualifying shard.
Single-shard queries are supported for:
-
Equality and In-list, such as
Area = ‘West’ -
Conditions containing literal, bind, or expression of literals and binds, such as
Area = :bind Area = CASE :bind <10 THEN ‘West’ ELSE ‘East’ END -
SELECT,UPDATE,DELETE,INSERT,FOR UPDATE, andMERGE.UPSERTis not supported.