The SUBPLAN FILTER operator is used to drive the execution of subqueries within an expression.
In OceanBase AI Database, the NESTED LOOP algorithm is used to execute the SUBPLAN FILTER operator. During execution, it fetches a row of data from the left and then executes the subplan on the right. The SUBPLAN FILTER operator can drive both non-correlated and correlated subquery evaluations, and the execution methods differ for each.
Driving non-correlated subquery evaluation
Example 1: The SUBPLAN FILTER operator drives the evaluation of a non-correlated subquery.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+NO_REWRITE*/c1 FROM t1 WHERE c2 > (SELECT MAX(c2) FROM t2);
+---------------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |SUBPLAN FILTER | |1 |5 | |
| |1 |├─TABLE FULL SCAN |t1 |1 |3 | |
| |2 |└─SCALAR GROUP BY | |1 |3 | |
| |3 | └─TABLE FULL SCAN|t2 |1 |3 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1]), filter(nil), rowset=16 |
| exec_params_(nil), onetime_exprs_([subquery(1)(:0)]), init_plan_idxs_(nil), use_batch=false |
| 1 - output([t1.c1]), filter([t1.c2 > :0]), rowset=16 |
| access([t1.c2], [t1.c1]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T_FUN_MAX(T_FUN_MAX(t2.c2))]), filter(nil), rowset=16 |
| group(nil), agg_func([T_FUN_MAX(T_FUN_MAX(t2.c2))]) |
| 3 - output([T_FUN_MAX(t2.c2)]), filter(nil), rowset=16 |
| access([t2.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true, |
| pushdown_aggregation([T_FUN_MAX(t2.c2)]) |
+---------------------------------------------------------------------------------------------------+
23 rows in set
In the preceding example, operator 0 (SUBPLAN FILTER) in the execution plan drives the execution of the right SCALAR GROUP BY subplan. The outputs & filters section shows the output information of the SUBPLAN FILTER operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. In the example, no filter is set for the SUBPLAN FILTER operator, so the value is nil. |
| exec_params_ | The right subplan depends on parameters of the left subplan, which are obtained from the left subplan by SUBPLAN FILTER at execution time and passed to the right subplan for execution. In the example, the SUBPLAN FILTER operator drives a non-correlated subquery and does not involve this parameter, so the value is nil. |
| onetime_exprs_ | Expressions that are evaluated only once in a plan. If the right subplan is a non-correlated subquery, the result is the same on each repeated execution. Therefore, after being evaluated once, the result is stored in the parameter set. Each time SUBPLAN FILTER is executed, it can directly obtain the execution result of the right subplan from the parameter set. The parameter subquery(1) indicates that the first subplan on the right of SUBPLAN FILTER is onetime expr_. |
| init_plan_idxs_ | The subplans that need to be executed only once in this operator. The difference from onetime_exprs_ is that init_plan_ returns multiple rows and columns, while onetime_expr_ returns a single row and column. In the example, this option is not set in the SQL query, so the value is nil. |
The general execution process for the SUBPLAN FILTER operator driving non-correlated subquery evaluation is as follows:
When starting,
SUBPLAN FILTERexecutesonetime_exprs_.It obtains the result of the non-correlated subquery on the right from the parameters, pushes the
filterdown to the left plan, and executes the left query.It outputs the rows of the left query.
Driving correlated subquery evaluation
Example 2: The SUBPLAN FILTER operator drives the evaluation of a correlated subquery.
obclient> EXPLAIN SELECT /*+NO_REWRITE*/c1 FROM t1 WHERE c2 > (SELECT MAX(c2) FROM t2 WHERE t1.c1=t2.c1);
+---------------------------------------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------------------------------------+
| =================================================== |
| |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| |
| --------------------------------------------------- |
| |0 |SUBPLAN FILTER | |1 |5 | |
| |1 |├─TABLE FULL SCAN |t1 |1 |3 | |
| |2 |└─SCALAR GROUP BY | |1 |3 | |
| |3 | └─TABLE FULL SCAN|t2 |1 |3 | |
| =================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.c1]), filter([t1.c2 > subquery(1)]), rowset=16 |
| exec_params_([t1.c1(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false |
| 1 - output([t1.c1], [t1.c2]), filter(nil), rowset=16 |
| access([t1.c1], [t1.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, |
| range_key([t1.__pk_increment]), range(MIN ; MAX)always true |
| 2 - output([T_FUN_MAX(T_FUN_MAX(t2.c2))]), filter(nil), rowset=16 |
| group(nil), agg_func([T_FUN_MAX(T_FUN_MAX(t2.c2))]) |
| 3 - output([T_FUN_MAX(t2.c2)]), filter([:0 = t2.c1]), rowset=16 |
| access([t2.c1], [t2.c2]), partitions(p0) |
| is_index_back=false, is_global_index=false, filter_before_indexback[false], |
| range_key([t2.__pk_increment]), range(MIN ; MAX)always true, |
| pushdown_aggregation([T_FUN_MAX(t2.c2)]) |
+---------------------------------------------------------------------------------------------+
23 rows in set
In the preceding example, operator 0 (SUBPLAN FILTER) in the execution plan drives the execution of the right SCALAR GROUP BY subplan. The outputs & filters section shows the output information of the SUBPLAN FILTER operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. For example, in Example 2, the filter condition of the SQL query is t1.c2 > subquery(1). |
| exec_params_ | The right subplan depends on parameters of the left subplan, which are obtained from the left subplan by SUBPLAN FILTER at execution time and passed to the right subplan for execution. Parameters that need to be pushed down after the left side outputs a row of data are generally not pushed down in non-correlated subqueries. |
| onetime_exprs_ | Expressions that are evaluated only once in a plan. If the right subplan is a non-correlated subquery, the result is the same on each repeated execution. Therefore, after being evaluated once, the result is stored in the parameter set. Each time SUBPLAN FILTER is executed, it can directly obtain the execution result of the right subplan from the parameter set. The parameter subquery(1) indicates that the first subplan on the right of SUBPLAN FILTER is onetime expr_. In the example, this option is not set in the SQL query, so the value is nil. |
| init_plan_idxs_ | The subplans that need to be executed only once in this operator. The difference from onetime_exprs_ is that init_plan_ returns multiple rows and columns, while onetime_expr_ returns a single row and column. In the example, this option is not set in the SQL query, so the value is nil. |
The general execution process for the SUBPLAN FILTER operator driving correlated subquery evaluation is as follows:
When starting,
SUBPLAN FILTERexecutesonetime_exprs_.It executes the left query, outputs one row, calculates the correlation parameters, pushes them to the right, and executes the right subquery.
It executes the
filterand outputs the data rows that meet the conditions.