暂无图片
暂无图片
4
暂无图片
暂无图片
暂无图片
首页 / 国产集中库SQL能力评测 - 子查询

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

韩锋频道 2024-12-19
517

子查询（Subquery），是SQL查询中的一种，它允许一个查询嵌套在另一个查询中。子查询通常用在SELECT、INSERT、UPDATE或DELETE语句中，作为一个单独的查询单元来返回数据，这些数据可以被外部查询使用。子查询通常是数据库开发中自然逻辑的体现，但对于数据库而言会带来很大挑战。一方面，子查询可能使得数据库的查询优化器难以生成高效的执行计划，优化器需要考虑如何最有效地执行嵌套查询，这可能涉及到多个表的连接、复杂的条件逻辑等，这对于优化器挑战是很大的。另一方面，子查询可能会降低SQL代码的可读性和维护性，使得优化和调试变得更加困难，特别是层次嵌套很深的子查询。此外，子查询还可能会改变数据访问模式、若逻辑复杂还可能影响索引使用等等弊端。本文将对比不同数据库对子查询的处理方式差异。

【系列文章】

国产集中库SQL能力评测 - 访问路径

国产集中库SQL能力评测 - 表连接


1. 子查询分类

1).子查询分类
2).Oracle 示例
-- 【子查询位置】
-- 标量子查询
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;
-- 内联子查询
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,salary
from emp e1
where salary=(select min(salary) from emp e2 where e2.dept_id=e1.dept_id);
-- 反关联子查询
select emp_id,emp_name,salary
from emp e1
where salary not in (select min(salary) from emp e2 where e2.dept_id=e1.dept_id);
-- 非关联子查询
select count(*) from emp
where 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_name
from 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);
-- EXISTS
select * from emp e where exists ( select 1 from dept d where d.dept_id=e.dept_id);
-- ANY
select emp_name,salary from emp 
where salary > any(select avg(salary) from emp group by dept_id);
-- ALL
select emp_name,salary from emp 
where salary < all(select avg(salary) from emp group by dept_id);
-- SOME
select emp_name,salary from emp 
where salary > some(select avg(salary) from emp group by dept_id);

3).国产库支持情况
国产数据库（含MySQL）都支持了上述子查询写法，除了MySQL需要稍微调整下写法外，其他都可以无需修改直接使用。

2. 子查询优化

子查询有多种优化方式，下面以 Oracle 支持的子查询优化手段为目标，看看国产数据库（含MySQL）支持情况如何。特说明，国产数据库可能含有其他子查询优化手段，下文不代表国产数据库针对子查询的全部优化能力。
1).子查询展开/解嵌套
子查询展开是优化器处理带子查询的目标SQL的一种优化手段，它是指优化器不再将目标SQL中的子查询当作一个独立的处理单元来单独执行，而是将该子查询转换为它自身和外部查询之间等价的表连接。从而获得更优的执行计划。子查询展开有两种形式，一种是将子查询拆开(即将该子查询中的表、视图从子查询拿出来，然后和外部查询中的表、视图做表连接)；一种是不拆开但是会把该子查询转换为一个内嵌视图(Inline View)，然后再和外部查询中的表、视图做表连接。前者属于启发式查询转换，后者属于基于代价的转换。
子查询展开通常都会提高原SQL的执行效率，因为如果原SQL不做子查询展开，那么通常情况下该子查询就会在其执行计划的最后一步才执行，并且会走FILTER类型的执行计划，这也意味着对于外部查询所在结果集中的每一条记录，该子查询都会被当作一个独立的执行单元来执行一次，外部查询所在的结果集有多少条记录，该子查询就会被执行多少次(可以近似这么理解，实际上并不完全是这样)。这种执行方式的执行效率通常都不会太高，尤其是在子查询中包含两个或者两个以上表连接时，此时做子查询展开后的执行效率往往会比走FILTER类型的执行计划高很多，因为此时优化器就会有其他更多、更高效的执行路径(比如哈希连接)可以选择。
❖ Oracle
-- 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);
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-JOIN
SQL> 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-JOIN
SQL> 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 |
--------------------------------------------------------------------------------
* 示例中，关联谓词中存在子查询，优化器对互关联子查询的反嵌套，会将子查询构造出一个内联视图，并将内联视图与主查询中的表进行关联。这种转换属于启发式查询转换。


❖MySQL
-- IN/EXISTS
mysql> 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/EXISTS
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             |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------+-------+----------+-------------------------+
* 嵌套循环表连接+物化子查询

-- 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        |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------------+-------+----------+-------------+


❖ DM

-- IN/EXISTS
SQL> 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/EXISTS
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)

-- 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.SALARY
4         #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)


❖ Kingbase

-- 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)


❖ YashanDB

-- 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)|                                | +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

❖ Vastbase

-- 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..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/EXISTS
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)
                        
-- 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)

2).标量子查询合并
针对含有标量子查询的情况，优化器会尝试与主查询中的对象进行合并关联操作。

❖ Oracle

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 |
---------------------------------------------------------------------------------------

❖ MySQL

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  |
+----+--------------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+


❖ DM


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)

❖ Kingbase

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)

❖ YashanDB

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)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+


❖ Vastbase

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)


3).子查询合并

当优化器未对子查询做反嵌套的情况下，可以将两个兼容的子查询合并为一个子查询。

❖ Oracle

SQL> explain plan for
select * from dual 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%'); 
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 |
-------------------------------------------------------------------------------------


❖ MySQL

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) |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-------------------------------------------------------------------------+


❖ DM

SQL> explain select * from dual 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%');
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)


❖ Kingbase

SQL> explain select * from dual 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%');
                              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)


❖ YashanDB

SQL> explain plan for
select * from dual 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 | 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)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+


❖ Vastbase

SQL> explain select * from dual 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%');
                            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)


4).子查询推入
子查询推入是一项对未能合并或者反嵌套的子查询优化的补充优化技术。通常情况下，未能合并或者反嵌套的子查询的子计划会被放置在整个查询计划的最后步骤执行，而子查询推进使得子查询能够提前被评估，使之可以出现在整体执行计划的较早步骤，从而获得更优的执行计划。

❖ Oracle

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，子查询会被最后执行



❖ MySQL

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 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+-------+----------+-------------+



❖ DM


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)

❖ Kingbase

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)



❖ YashanDB

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)|                                |
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+



❖ Vastbase

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)



5).简单谓词推入
简单过滤谓词推入，即简单地将主查询中作用于子查询的过滤谓词推入子查询中。它是属于启发式查询转换技术，只要满足条件就会进行转换。

❖ Oracle

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)



❖ MySQL

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)) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+



❖ DM

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 < var1
4         #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)



❖ Kingbase

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)



❖ YashanDB

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)



❖ Vastbase

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)



6).子查询谓词迁移

谓词迁移是指在含有多个子查询的复杂查询中，将其中一个子查询的谓词条件提取出来，并推入另外的子查询中，成为谓词的一部分。

❖ Oracle

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步的分组判断中，已入后面子查询中的谓词条件，提前做了过滤


❖ MySQL

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))) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


❖ DM

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)


❖ Kingbase

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)


❖ YashanDB

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])


❖ Vastbase

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_id
               ->  Seq Scan on emp  (cost=0.00..243.00 rows=181 width=16)
                     Filter: (dept_id = ANY ('{10,20}'::number[]))









sqlsql优化oracle达梦金仓数据库
文章转载自韩锋频道，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论