有依赖关系的子查询是指该子查询的执行依赖了外部查询的“变量”,所以这种子查询通常会被计算多次。
如下分别为没有依赖关系的子查询和有依赖关系的子查询的示例。
如下分别为被改写成连接语句的子查询和没有被改写成连接语句的子查询的示例。
Query OK, 0 rows affected (0.70 sec)
OceanBase (root@test)> create table t2(a int primary key, b int, c int);
Query OK, 0 rows affected (0.92 sec)
-- 有依赖关系的子查询被改写成了semi-join,并且使用了hash semi-join来实现
OceanBase (root@test)> explain select * from t1 where t1.a in (select t2.c from t2 where t2.b = t1.b);
| =======================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------
|0 |HASH SEMI JOIN| |1 |2924|
|1 | TABLE SCAN |t1 |1000 |455 |
|2 | TABLE SCAN |t2 |1000 |455 |
=======================================
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c]), filter(nil),
equal_conds([t1.a = t2.c], [t2.b = t1.b]), other_conds(nil)
1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
access([t1.b], [t1.a], [t1.c]), partitions(p0)
2 - output([t2.b], [t2.c]), filter(nil),
access([t2.b], [t2.c]), partitions(p0)
-- 有依赖关系的子查询不能被改写成semi-join,使用了subplan filter来实现
OceanBase (root@test)> explain select * from t1 where t1.a > (select sum(t2.c) from t2 where t2.b = t1.b);
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------------
|0 |SUBPLAN FILTER | |334 |207683|
|1 | TABLE SCAN |t1 |334 |176 |
|3 | TABLE SCAN |t2 |2 |622 |
===========================================
Outputs & filters:
-------------------------------------
0 - output([t1.a], [t1.b], [t1.c]), filter([t1.a > subquery(1)]),
exec_params_([t1.b]), onetime_exprs_(nil), init_plan_idxs_(nil)
1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
access([t1.b], [t1.a], [t1.c]), partitions(p0)
2 - output([T_FUN_SUM(t2.c)]), filter(nil),
group(nil), agg_func([T_FUN_SUM(t2.c)])
3 - output([t2.c]), filter([t2.b = ?]),
access([t2.b], [t2.c]), partitions(p0)