Optimizer #

SphereEx-DBPlusEngine offers two optimizers: the slice-down push engine and the federated query engine. The slice-down push engine is suitable for cases where statements are ready to be executed on a slice. On the other hand, the federated query engine can optimize a wider range of scenarios. It excels at optimizing complex queries, such as correlation queries and subqueries, and also supports distributed queries across multiple database instances. Internally, the federated query engine utilizes relational algebra to optimize the query plan and query the results through the optimal plan.

However, it is worth noting that the federated query optimization engine is currently a trial feature. While basic functionality is available, there is still room for improvement.

Enabling the Federated Query Engine #

To use the federated query engine, which is not enabled by default, you need to modify a parameter setting to activate it.

sql-federation-enabled = true

Available Federated Search Scenarios #

The federated query engine is designed to handle scenarios where the exact slice cannot be accessed. To illustrate this, consider the following example.

Sub-queries

The federated query engine is particularly useful when the subquery and the enclosing query don’t specify the same slice key, or when the slice key values don’t match.

SELECT * FROM (SELECT * FROM t_order) o;
SELECT * FROM (SELECT * FROM t_order) o WHERE o.order_id = 1;
SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o;
SELECT * FROM (SELECT * FROM t_order WHERE order_id = 1) o WHERE o.order_id = 2;

Cross-database federated Queries

The federated query engine can also handle association queries that involve multiple tables distributed across different database instances. For example, if t_order and t_order_item are sharded tables with multiple data nodes and no binding table rules are configured, while t_user and t_user_role are single tables distributed across different database instances, the federated query engine can support the following frequently used association queries:

SELECT * FROM t_order o INNER JOIN t_order_item i ON o.order_id = i.order_id WHERE o.order_id = 1;
SELECT * FROM t_order o INNER JOIN t_user u ON o.user_id = u.user_id WHERE o.user_id = 1;
SELECT * FROM t_order o LEFT JOIN t_user_role r ON o.user_id = r.user_id WHERE o.user_id = 1;
SELECT * FROM t_order_item i LEFT JOIN t_user u ON i.user_id = u.user_id WHERE i.user_id = 1;
SELECT * FROM t_order_item i RIGHT JOIN t_user_role r ON i.user_id = r.user_id WHERE i.user_id = 1;
SELECT * FROM t_user u RIGHT JOIN t_user_role r ON u.user_id = r.user_id WHERE u.user_id = 1;