国产集中库SQL能力评测 – 子查询

-- 【子查询位置】-- 标量子查询select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_namefrom emp e where e.emp_id=1;-- 内联子查询select * from (select * from emp where salary<1500) where dept_id <50;-- 嵌套子查询select * from emp where salary=(select max(salary) from emp); -- 【与主查询关联】-- 关联子查询select emp_id,emp_name,salaryfrom emp e1where salary=(select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 反关联子查询select emp_id,emp_name,salaryfrom emp e1where salary not in (select min(salary) from emp e2 where e2.dept_id=e1.dept_id);-- 非关联子查询select count(*) from empwhere salary<(select avg(salary) from emp); -- [子查询结果集]-- 标量子查询select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_namefrom emp e where e.emp_id=1;-- 列子查询select * from emp where dept_id in (select dept_id from dept where dept_name like dept1%);-- 行子查询select * from emp a where a.dept_id in (select b.dept_id from dept b);-- 表子查询select a.emp_id,a.dept_id,a.salary from emp a where (a.dept_id ,a.salary) in (select b.dept_id,b.salary from emp b where b.salary<1300); -- [子查询谓词]-- IN select * from emp where dept_id in (select dept_id from dept where dept_id <20);-- EXISTSselect * from emp e where exists ( select 1 from dept d where d.dept_id=e.dept_id);-- ANYselect emp_name,salary from emp where salary > any(select avg(salary) from emp group by dept_id);-- ALLselect emp_name,salary from emp where salary < all(select avg(salary) from emp group by dept_id);-- SOMEselect emp_name,salary from emp where salary > some(select avg(salary) from emp group by dept_id);1.2.3.4.

-- IN/EXISTS转换为SEMI JOINSQL> explain plan for select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 332K| 15 (0)| 00:00:01 || 1 | NESTED LOOPS SEMI | | 10000 | 332K| 15 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 302K| 15 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN| DEPT_PK | 100 | 300 | 0 (0)| 00:00:01 |------------------------------------------------------------------------------* 优化器将IN或EXISTS子句中的子查询展开(反嵌套)，使得优化器选择半关联(SEMI-JOIN)操作。这种转换属于启发式查询转换。 -- NOT IN/EXISTS转换为ANTI-JOINSQL> explain plan for select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 3400 | 15 (0)| 00:00:01 || 1 | NESTED LOOPS ANTI | | 100 | 3400 | 15 (0)| 00:00:01 || 2 | TABLE ACCESS FULL| EMP | 10000 | 302K| 15 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN| DEPT_PK | 100 | 300 | 0 (0)| 00:00:01 |------------------------------------------------------------------------------* 优化器将NOT IN或NOT EXISTS子句中的子查询展开(反嵌套)，使得优化器选择反关联(ANTI-JOIN)操作。这种转换属于基于代价的查询转换。 -- NOT IN/NOT EXISTS转换为Null-Aware ANTI-JOINSQL> explain plan for select * from emp e where e.dept_id not in (select dept_id from dept d);SQL> select * from table(dbms_xplan.display);------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 3400 | 17 (6)| 00:00:01 ||* 1 | HASH JOIN RIGHT ANTI SNA| | 100 | 3400 | 17 (6)| 00:00:01 || 2 | INDEX FULL SCAN | DEPT_PK | 100 | 300 | 1 (0)| 00:00:01 || 3 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |------------------------------------------------------------------------------------* 示例中EMP表的DEPT_ID字段允许为空，优化器将NOT IN/NOT EXISTS子句中的子查询展开(反嵌套)，使得优化器能选择对空值敏感的反关联(Null-Aware ANTI-JOIN)操作。* 这种转换属于启发式查询转换。对空值敏感的反关联操作能在关联数据时关注到空值的存在，从而避免使用代价昂贵的操作(如笛卡尔积关联)来获取逻辑结果。 -- 互关联子查询转换为内联视图SQL> explain plan for select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 500 | 28500 | 32 (7)| 00:00:01 ||* 1 | HASH JOIN | | 500 | 28500 | 32 (7)| 00:00:01 || 2 | VIEW | VW_SQ_1 | 10000 | 253K| 16 (7)| 00:00:01 || 3 | HASH GROUP BY | | 10000 | 90000 | 16 (7)| 00:00:01 || 4 | TABLE ACCESS FULL| EMP | 10000 | 90000 | 15 (0)| 00:00:01 || 5 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |--------------------------------------------------------------------------------* 示例中，关联谓词中存在子查询，优化器对互关联子查询的反嵌套，会将子查询构造出一个内联视图，并将内联视图与主查询中的表进行关联。这种转换属于启发式查询转换。1.2.3.4.

-- IN/EXISTSmysql> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+* 退化为嵌套循环表连接 -- NOT IN/EXISTSmysql> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.e.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | d | NULL | index | PRIMARY | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+* 嵌套循环表连接+物化子查询 -- NOT IN/NOT EXISTS（NULL AWare）mysql> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 10109 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.e.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | d | NULL | index | PRIMARY | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+ -- 互关联子查询mysql> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+| 1 | PRIMARY | e1 | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using where || 2 | DEPENDENT SUBQUERY | e2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e1.emp_id | 1 | 100.00 | NULL |+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+1.2.3.4.

-- IN/EXISTSSQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 10000, 163]2 #PRJT2: [3, 10000, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 10000, 163]; n_keys(1) KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1) -- NOT IN/EXISTSSQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 1, 163]2 #PRJT2: [3, 1, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 1, 163]; n_keys(1) (ANTI), KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1) -- NOT IN/NOT EXISTS（NULL AWare）SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id);1 #NSET2: [3, 1, 163]2 #PRJT2: [3, 1, 163]; exp_num(6), is_atom(FALSE)3 #HASH RIGHT SEMI JOIN2: [3, 1, 163]; n_keys(1) (ANTI), KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #SSCN: [1, 100, 30]; INDEX33555481(DEPT as D); btr_scan(1); is_global(0)5 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E); btr_scan(1) -- 互关联子查询SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id);1 #NSET2: [6, 500, 223]2 #PRJT2: [6, 500, 223]; exp_num(6), is_atom(FALSE)3 #SLCT2: [6, 500, 223]; DMTEMPVIEW_889193621.colname < E1.SALARY4 #HASH2 INNER JOIN: [6, 500, 223]; RKEY_UNIQUE KEY_NUM(1); KEY(DMTEMPVIEW_889193621.colname=E1.EMP_ID) KEY_NULL_EQU(0)5 #PRJT2: [2, 10000, 60]; exp_num(2), is_atom(FALSE)6 #HAGR2: [2, 10000, 60]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(E2.EMP_ID)7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP as E2); btr_scan(1)8 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP as E1); btr_scan(1)1.2.3.4.

-- IN/EXISTS SQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- NOT IN/EXISTS SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ------------------------------------------------------------------------------------ Gather (cost=1003.25..17935.13 rows=9901 width=42) Workers Planned: 2 -> Hash Anti Join (cost=3.25..15945.03 rows=4125 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Parallel Seq Scan on emp e (cost=0.00..14342.67 rows=416667 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- NOT IN/NOT EXISTS（NULL AWare） SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN ------------------------------------------------------------------------------------ Gather (cost=1003.25..17935.13 rows=9901 width=42) Workers Planned: 2 -> Hash Anti Join (cost=3.25..15945.03 rows=4125 width=42) Hash Cond: (e.dept_id = d.dept_id) -> Parallel Seq Scan on emp e (cost=0.00..14342.67 rows=416667 width=42) -> Hash (cost=2.00..2.00 rows=100 width=5) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=5) -- 互关联子查询 SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); QUERY PLAN ----------------------------------------------------------------------------------- Seq Scan on emp e1 (cost=0.00..8480176.00 rows=333333 width=42) Filter: (salary > (SubPlan 1)) SubPlan 1 -> Aggregate (cost=8.45..8.46 rows=1 width=8) -> Index Scan using EMP_PK on emp e2 (cost=0.42..8.44 rows=1 width=8) Index Cond: (emp_id = e1.emp_id)1.

-- IN/EXISTS转换为SEMI JOIN SQL> explain plan for select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS SEMI | | | 10000| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- NOT IN/EXISTS转换为ANTI-JOIN SQL> explain plan for select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS ANTI | | | 1| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- NOT IN/NOT EXISTS转换为Null-Aware ANTI-JOIN SQL> explain plan for select * from emp e where e.dept_id not in (select dept_id from dept d); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS ANTI | | | 1| 47( 0)| | | 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ -- 互关联子查询转换为内联视图 SQL> explain plan for select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | | 1 | NESTED INDEX LOOPS INNER | | | 6650| 61( 0)| | | 2 | VIEW | | | 10000| 57( 0)| | | 3 | HASH GROUP | | | 10000| 57( 0)| | | 4 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| | |* 5 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| | |* 6 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.

-- IN/EXISTSSQL> explain select * from emp e where exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Join (cost=3.25..345.26 rows=9901 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- NOT IN/EXISTSSQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Anti Join (cost=3.25..253.43 rows=99 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- NOT IN/NOT EXISTS（NULL AWare）SQL> explain select * from emp e where not exists (select 1 from dept d where d.dept_id=e.dept_id); QUERY PLAN-------------------------------------------------------------------- Hash Anti Join (cost=3.25..253.43 rows=99 width=44) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44) -> Hash (cost=2.00..2.00 rows=100 width=8) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=8) -- 互关联子查询SQL> explain select * from emp e1 where salary >(select avg(salary) from emp e2 where e1.emp_id=e2.emp_id); QUERY PLAN------------------------------------------------------------------------- Hash Join (cost=611.00..998.50 rows=3333 width=44) Hash Cond: (e2.emp_id = e1.emp_id) Join Filter: (e1.salary > (avg(e2.salary))) -> HashAggregate (cost=268.00..393.00 rows=10000 width=48) Group By Key: e2.emp_id -> Seq Scan on emp e2 (cost=0.00..218.00 rows=10000 width=16) -> Hash (cost=218.00..218.00 rows=10000 width=44) -> Seq Scan on emp e1 (cost=0.00..218.00 rows=10000 width=44)1.

SQL> explain plan for select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 10 | 1 (0)| 00:00:01 ||* 2 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 7 | 2 (0)| 00:00:01 ||* 4 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |---------------------------------------------------------------------------------------1.

mysql> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | PRIMARY | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL || 2 | DEPENDENT SUBQUERY | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1.

SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;1 #NSET2: [1, 1, 72]2 #PIPE2: [1, 1, 72]3 #PRJT2: [1, 1, 72]; exp_num(4), is_atom(FALSE)4 #BLKUP2: [1, 1, 72]; INDEX33555485(E)5 #SSEK2: [1, 1, 72]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(1),exp_cast(1)], is_global(0)6 #SPL2: [1, 1, 78]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)7 #PRJT2: [1, 1, 78]; exp_num(1), is_atom(TRUE)8 #BLKUP2: [1, 1, 78]; INDEX33555481(D)9 #SSEK2: [1, 1, 78]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[var1,var1], is_global(0)1.

SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1; QUERY PLAN----------------------------------------------------------------------- Index Scan using EMP_PK on emp e (cost=0.42..10.69 rows=1 width=229) Index Cond: (emp_id = 1::numeric) SubPlan 1 -> Seq Scan on dept d (cost=0.00..2.25 rows=1 width=13) Filter: (dept_id = e.dept_id)1.

SQL> explain plan for select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | SUBQUERY | QUERY[1] | | | | || 2 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 3 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| ||* 5 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.

SQL> explain select e.emp_id,e.dept_id,(select dept_name from dept d where d.dept_id=e.dept_id) dept_name from emp e where e.emp_id=1; QUERY PLAN------------------------------------------------------------------------------- Index Scan using emp_pk on emp e (cost=0.00..16.54 rows=1 width=16) Index Cond: (emp_id = 1::number) SubPlan 1 -> Index Scan using dept_pk on dept d (cost=0.00..8.27 rows=1 width=12) Index Cond: (dept_id = e.dept_id)1.

SQL> explain plan forselect * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100) and exists( select 1 from emp e2 where e2.emp_name like emp2%); SQL> select * from table(dbms_xplan.display);-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 2 | 6 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_EMP_NAME | 2 | 16 | 2 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | 2 | 10 | 2 (0)| 00:00:01 |-------------------------------------------------------------------------------------1.

mysql> explain select * from dual_tab d -> where exists -> ( select 1 from emp e1 where e1.salary<1100) -> and exists -> ( select 1 from emp e2 where e2.emp_name like emp2%);+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+| 1 | SIMPLE | d | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL || 1 | SIMPLE | e1 | NULL | range | idx_emp_salary | idx_emp_salary | 5 | NULL | 459 | 100.00 | Using where; Using index; FirstMatch(d); Using join buffer (hash join) || 1 | SIMPLE | e2 | NULL | range | idx_emp_name | idx_emp_name | 33 | NULL | 1111 | 100.00 | Using where; Using index; FirstMatch(e1); Using join buffer (hash join) |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+1.

SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like emp2%);1 #NSET2: [1, 1, 48]2 #PIPE2: [1, 1, 48]3 #PIPE2: [1, 1, 48]4 #PRJT2: [1, 1, 48]; exp_num(1), is_atom(FALSE)5 #SLCT2: [1, 1, 48]; (NOREFED_EXISTS_SSS AND NOREFED_EXISTS_SSS)6 #CSCN2: [1, 1, 48]; SYSINDEXSYSDUAL2(SYSDUAL2 as D); btr_scan(1)7 #SPL2: [1, 1111, 48]; key_num(1), spool_num(1), is_atom(FALSE), has_var(0), sites(-)8 #PRJT2: [1, 1111, 48]; exp_num(1), is_atom(FALSE)9 #SSEK2: [1, 1111, 48]; scan_type(ASC), IDX_EMP_NAME(EMP as E2), scan_range[emp2,emp3), is_global(0)10 #SPL2: [1, 885, 30]; key_num(1), spool_num(0), is_atom(FALSE), has_var(0), sites(-)11 #PRJT2: [1, 885, 30]; exp_num(1), is_atom(FALSE)12 #SSEK2: [1, 885, 30]; scan_type(ASC), IDX_EMP_SALARY(EMP as E1), scan_range(null2,exp_cast(1100)), is_global(0)1.

SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like emp2%); QUERY PLAN---------------------------------------------------------------------- Result (cost=227.01..228.02 rows=1 width=2) One-Time Filter: ($0 AND $1) InitPlan 1 (returns $0) -> Seq Scan on emp e1 (cost=0.00..22676.00 rows=91272 width=0) Filter: (salary < 1100::double precision) InitPlan 2 (returns $1) -> Seq Scan on emp e2 (cost=0.00..22676.00 rows=100 width=0) Filter: ((emp_name)::text ~~ emp2%::text) -> Seq Scan on dual d (cost=227.01..228.02 rows=1 width=2)1.

SQL> explain plan forselect * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100) and exists( select 1 from emp e2 where e2.emp_name like emp2%); +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED LOOPS SEMI | | | 1| 12( 0)| || 2 | NESTED LOOPS SEMI | | | 1| 10( 0)| || 3 | TABLE ACCESS FULL | X$DUAL | SYS | 1| 8( 0)| ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 2( 0)| ||* 5 | INDEX RANGE SCAN | IDX_EMP_NAME | TESTUSER | 1094| 2( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.

SQL> explain select * from dual dwhere exists( select 1 from emp e1 where e1.salary<1100)and exists( select 1 from emp e2 where e2.emp_name like emp2%); QUERY PLAN------------------------------------------------------------------ Result (cost=243.27..243.29 rows=1 width=32) One-Time Filter: ($0 AND $1) InitPlan 1 (returns $0) -> Seq Scan on emp e1 (cost=0.00..243.00 rows=900 width=0) Filter: (salary < 1100::double precision) InitPlan 2 (returns $1) -> Seq Scan on emp e2 (cost=0.00..243.00 rows=1 width=0) Filter: ((emp_name)::text ~~ emp2%::text) -> Result (cost=0.00..0.01 rows=1 width=0)1.

SQL> explain plan for select * from emp e where salary >(select avg(salary) from emp);SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 500 | 15500 | 24 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| EMP | 500 | 15500 | 14 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_EMP_SALARY | 81 | | 2 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 5 | | || 4 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |---------------------------------------------------------------------------------------------- SQL> explain plan for select /*+ no_push_subq(@inv)*/ * from emp e where salary >(select /*+ qb_name(inv)*/ avg(salary) from emp);SQL> select * from table(dbms_xplan.display);-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 302K| 25 (0)| 00:00:01 ||* 1 | FILTER | | | | | || 2 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 || 3 | SORT AGGREGATE | | 1 | 5 | | || 4 | INDEX FAST FULL SCAN| IDX_EMP_SALARY | 10000 | 50000 | 10 (0)| 00:00:01 |-----------------------------------------------------------------------------------------* 如禁用子查询推入功能，执行计划则退化为FILTER，子查询会被最后执行1.2.

mysql> explain select * from emp e where salary >(select avg(salary) from emp);+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+| 1 | PRIMARY | e | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10117 | 44.77 | Using where || 2 | SUBQUERY | emp | NULL | index | NULL | idx_emp_salary | 5 | NULL | 10117 | 100.00 | Using index |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+1.

SQL> explain select * from emp e where salary >(select avg(salary) from emp);1 #NSET2: [1, 500, 163]2 #PIPE2: [1, 500, 163]3 #PRJT2: [1, 500, 163]; exp_num(6), is_atom(FALSE)4 #BLKUP2: [1, 500, 163]; IDX_EMP_SALARY(E)5 #SSEK2: [1, 500, 163]; scan_type(ASC), IDX_EMP_SALARY(EMP as E), scan_range(exp48,max], is_global(0)6 #SPL2: [1, 1, 30]; key_num(1), spool_num(0), is_atom(TRUE), has_var(0), sites(-)7 #PRJT2: [1, 1, 30]; exp_num(1), is_atom(TRUE)8 #AAGR2: [1, 1, 30]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)9 #SSCN: [1, 10000, 30]; IDX_EMP_SALARY(EMP); btr_scan(1); is_global(0)1.

SQL> explain select * from emp e where salary >(select avg(salary) from emp); QUERY PLAN----------------------------------------------------------------------------------------------- Bitmap Heap Scan on emp e (cost=24352.32..38694.98 rows=333333 width=42) Recheck Cond: (salary > $1) InitPlan 1 (returns $1) -> Finalize Aggregate (cost=16384.55..16384.56 rows=1 width=8) -> Gather (cost=16384.33..16384.54 rows=2 width=32) Workers Planned: 2 -> Partial Aggregate (cost=15384.33..15384.34 rows=1 width=32) -> Parallel Seq Scan on emp (cost=0.00..14342.67 rows=416667 width=8) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..7884.42 rows=333333 width=0) Index Cond: (salary > $1)1.

SQL> explain plan for select * from emp e where salary >(select avg(salary) from emp);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | SUBQUERY | QUERY[1] | | | | || 2 | AGGREGATE | | | 1| 27( 0)| || 3 | INDEX FAST FULL SCAN | IDX_EMP_SALARY | TESTUSER | 10000| 26( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 3301| 54( 0)| ||* 5 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 3301| 9( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.

SQL> explain select * from emp e where salary >(select avg(salary) from emp); QUERY PLAN----------------------------------------------------------------------- Seq Scan on emp e (cost=243.01..486.01 rows=3333 width=44) Filter: (salary > $0) InitPlan 1 (returns $0) -> Aggregate (cost=243.00..243.01 rows=1 width=40) -> Seq Scan on emp (cost=0.00..218.00 rows=10000 width=8)1.

SQL> explain plan for select * from (select * from emp where salary<1100) v where dept_id <10;SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 80 | 2480 | 15 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| EMP | 80 | 2480 | 15 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("EMP"."DEPT_ID"<10 AND "SALARY"<1100)1.

mysql> explain select * from (select * from emp where salary<1100) v where dept_id <10;+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+| 1 | SIMPLE | emp | NULL | range | idx_emp_salary | idx_emp_salary | 5 | NULL | 459 | 33.33 | Using index condition; Using where |+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+1 row in set, 1 warning (0.00 sec)mysql> show warnings;+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `testdb`.`emp`.`emp_id` AS `emp_id`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_name` AS `emp_name`,`testdb`.`emp`.`birthday` AS `birthday`,`testdb`.`emp`.`salary` AS `salary` from `testdb`.`emp` where ((`testdb`.`emp`.`dept_id` < 10) and (`testdb`.`emp`.`salary` < 1100)) |+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1.

SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10;1 #NSET2: [1, 44, 163]2 #PRJT2: [1, 44, 163]; exp_num(6), is_atom(FALSE)3 #SLCT2: [1, 44, 163]; EMP.DEPT_ID < var14 #BLKUP2: [1, 885, 163]; IDX_EMP_SALARY(EMP)5 #SSEK2: [1, 885, 163]; scan_type(ASC), IDX_EMP_SALARY(EMP), scan_range(null2,exp_cast(1100)), is_global(0)1.

SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10; QUERY PLAN------------------------------------------------------------------------------------ Bitmap Heap Scan on emp (cost=2162.79..13707.87 rows=7293 width=42) Recheck Cond: (salary < 1100::double precision) Filter: (dept_id < 10::numeric) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..2160.97 rows=91272 width=0) Index Cond: (salary < 1100::double precision)1.

SQL> explain plan for select * from (select * from emp where salary<1100) v where dept_id <10; +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 0 | SELECT STATEMENT | | | | | | |* 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 277| 7( 0)| | |* 2 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 920| 3( 0)| | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ Operation Information (identified by operation id): --------------------------------------------------- 1 - Predicate : filter("EMP"."DEPT_ID" < 10) 2 - Predicate : access("EMP"."SALARY" < 1100)1.

SQL> explain select * from (select * from emp where salary<1100) v where dept_id <10; QUERY PLAN -------------------------------------------------------------------------------- Bitmap Heap Scan on emp (cost=23.02..154.52 rows=76 width=44) Recheck Cond: (salary < 1100::double precision) Filter: (dept_id < 10::number) -> Bitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0) Index Cond: (salary < 1100::double precision) explain select * from (select * from emp where salaryBitmap Index Scan on idx_emp_salary (cost=0.00..23.00 rows=900 width=0)\n Index Cond: (salary1.

SQL> explain plan for select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; SQL> select * from table(dbms_xplan.display); ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 132 | 20 (5)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 4 | 132 | 20 (5)| 00:00:01 | | 3 | VIEW | | 2 | 52 | 16 (7)| 00:00:01 | | 4 | HASH GROUP BY | | 2 | 14 | 16 (7)| 00:00:01 | |* 5 | TABLE ACCESS FULL | EMP | 199 | 1393 | 15 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | IDX_EMP_DEPTID | 2 | | 1 (0)| 00:00:01 | | 7 | TABLE ACCESS BY INDEX ROWID| EMP | 2 | 14 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("DEPT_ID"=10 OR "DEPT_ID"=20) 6 - access("V1"."DEPT_ID"="DEPT_ID") filter("DEPT_ID"=10 OR "DEPT_ID"=20) * 在第5步的分组判断中，已入后面子查询中的谓词条件，提前做了过滤1.

mysql> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 20.00 | Using where || 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | testdb.emp.dept_id | 10 | 100.00 | NULL || 2 | DERIVED | emp | NULL | ALL | NULL | NULL | NULL | NULL | 10117 | 100.00 | Using temporary |+----+-------------+------------+------------+------+---------------+-------------+---------+--------------------+-------+----------+-----------------+ mysql> show warnings;+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Note | 1003 | /* select#1 */ select `v1`.`dept_id` AS `dept_id`,`v1`.`min(emp_id)` AS `min(emp_id)`,`testdb`.`emp`.`dept_id` AS `dept_id`,`testdb`.`emp`.`emp_id` AS `emp_id` from (/* select#2 */ select `testdb`.`emp`.`dept_id` AS `dept_id`,min(`testdb`.`emp`.`emp_id`) AS `min(emp_id)` from `testdb`.`emp` group by `testdb`.`emp`.`dept_id`) `v1` join `testdb`.`emp` where ((`v1`.`dept_id` = `testdb`.`emp`.`dept_id`) and (`testdb`.`emp`.`dept_id` in (10,20))) |+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1.2.

SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;1 #NSET2: [6, 54, 162]2 #PRJT2: [6, 54, 162]; exp_num(4), is_atom(FALSE)3 #HAGR2: [6, 54, 162]; grp_num(4), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(TMP_PHA_ALIAS_16778408.DEPT_ID, EMP.EMP_ID, EMP.DEPT_ID, EMP.ROWID)4 #HASH RIGHT SEMI JOIN2: [5, 73, 162]; n_keys(1) KEY(DMTEMPVIEW_889193644.colname=EMP.DEPT_ID) KEY_NULL_EQU(0)5 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)6 #HASH2 INNER JOIN: [5, 73, 162]; KEY_NUM(1); KEY(TMP_PHA_ALIAS_16778408.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)7 #HASH2 INNER JOIN: [2, 500, 90]; KEY_NUM(1); KEY(DMTEMPVIEW_889193642.colname=TMP_PHA_ALIAS_16778408.DEPT_ID) KEY_NULL_EQU(0)8 #CONST VALUE LIST: [1, 2, 30]; row_num(2), col_num(1)9 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP as TMP_PHA_ALIAS_16778408); btr_scan(1)10 #CSCN2: [1, 10000, 72]; INDEX33555484(EMP); btr_scan(1)1.

SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; QUERY PLAN---------------------------------------------------------------------------------------------------------------- Hash Join (cost=18458.26..35835.65 rows=19400 width=48) Hash Cond: (emp.dept_id = emp_1.dept_id) -> Gather (cost=1000.00..18324.33 rows=19400 width=11) Workers Planned: 2 -> Parallel Seq Scan on emp (cost=0.00..15384.33 rows=8083 width=11) Filter: (dept_id = ANY ({10,20}::numeric[])) -> Hash (cost=17457.00..17457.00 rows=101 width=37) -> Finalize GroupAggregate (cost=17430.40..17455.99 rows=101 width=37) Group Key: emp_1.dept_id -> Gather Merge (cost=17430.40..17453.97 rows=202 width=37) Workers Planned: 2 -> Sort (cost=16430.37..16430.63 rows=101 width=37) Sort Key: emp_1.dept_id -> Partial HashAggregate (cost=16426.00..16427.01 rows=101 width=37) Group Key: emp_1.dept_id -> Parallel Seq Scan on emp emp_1 (cost=0.00..14342.67 rows=416667 width=11)1.

SQL> explain plan for select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN INNER | | | 204| 96( 0)| || 2 | JOIN FILTER USE | | | 100| 48( 0)| || 3 | VIEW | | | 100| 48( 0)| || 4 | HASH GROUP | | | 100| 48( 0)| ||* 5 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| ||* 6 | JOIN FILTER CREATE | | | 201| 46( 0)| ||* 7 | TABLE ACCESS FULL | EMP | TESTUSER | 201| 46( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+Operation Information (identified by operation id):--------------------------------------------------- 1 - Predicate : access("V1"."DEPT_ID" = "EMP"."DEPT_ID") 4 - Group Expression: ("EMP"."DEPT_ID") 5 - Predicate : RUNTIME FILTER(RUNTIME USE(0): "EMP"."DEPT_ID") 6 - Predicate : RUNTIME FILTER(RUNTIME CREATE(0): "EMP"."DEPT_ID") 7 - Predicate : filter("EMP"."DEPT_ID" IN [10, 20])1.

SQL> explain select * from (select dept_id,min(emp_id) from emp group by dept_id) v1,(select dept_id,emp_id from emp where dept_id in (10,20)) v2 where v1.dept_id=v2.dept_id; QUERY PLAN-------------------------------------------------------------------------- Nested Loop (cost=243.90..492.38 rows=181 width=56) Join Filter: (testuser.emp.dept_id = testuser.emp.dept_id) -> Seq Scan on emp (cost=0.00..243.00 rows=181 width=16) Filter: (dept_id = ANY ({10,20}::number[])) -> Materialize (cost=243.90..243.95 rows=2 width=40) -> HashAggregate (cost=243.90..243.92 rows=2 width=48) Group By Key: testuser.emp.dept_id1.