国产集中库SQL能力评测 – 表连接
表连接，是指在一个SQL语句中通过表与表之间的关联，从一个或多个表检索出相关的数据。连接是通过SQL语句中FROM从句的多个表名，以及WHERE从句里定义的表之间的连接条件来实现的。如果一个SQL语句的关联表超过两个，那么连接的顺序如何呢？数据库会首先连接其中的两个表，产生一个结果集；然后将产生的结果集与下一个表再进行关联；继续这个过程，直到所有的表都连接完成；最后产生所需的数据。本文将对比不同数据库对表连接的处理方式差异。
1. 表连接的几个知识
1).表连接分类
图片
上面是 SQL99 语法标准，从语法定义来看，我们可以把表连接分为几种情况。其中，笛卡尔连接比较少见，自然连接为一种语法糖写法，因此下面重点会谈谈其他四种连接。
图片
我们可以用一张图来简单理解下
图片
2).表连接处理方式
在表连接的实现方式上，可以有很多种，包括但不限于：嵌套循环(NESTED LOOP JOIN)、群集连接(CLUSTER JOIN)、排序合并连接(SORT MERGE JOIN)、笛卡尔连接(CARTESIAN JOIN)、哈希连接(HASH JOIN) 、索引连接(INDEX JOIN) 等等。这里比较常见的是：哈希连接(简称 HJ)、嵌套循环（简称 NJ）、排序合并（简称SMJ）。下面以Oracle为例说明下常见的这三种方式。
图片
2. Oracle 表连接能力说明
1)Oracle 表连接说明
嵌套循环优化器决定优先被执行的外侧表。这个外侧表也被成为驱动表，以驱动表查询范围中的每一行数据为基准从内侧表中寻找满足查询条件的数据行。在从驱动表中读取满足某些条件的数据行之后，SQL语句中所使用到的关于该驱动表的所有列都获得常量，通过对这些常量和原来就赋予了常量的查询条件的斟酌，为下一步执行选择一个内侧表。如果仍然还存在需要按照该方法执行连接的表，则按照与上面相同的方法来决定连接顺序。如果连接列上有索引，则会对优化器决定连接顺序有重要影响。实际在连接时，需要读取与外侧集合汇中的每一行数据相对应的内侧集合中的所有行。如果可以通过部分范围处理的方式来处理，则在执行过程中可以临时中止连接。对于外侧循环中的每一个数据行，内侧循环中所有满足条件的数据行都需要被执行。
排序合并在排序合并连接中，oracle分别将第一源表、第二源表按它们各自要连接的列排序，然后将已经排序的两个源表合并。如果找到匹配的数据，就放到结果集。排序合并是在扫描的同时执行连接，而不是像嵌套循环连接那样为了执行表连接而需要按随机方式读取数据。为了实现表连接，必须先对两个表中将要执行连接的行进行排序。虽然这个方法提高了连接的效率，但是由于排序的存在，也增加了连接的代价。引入排序合并连接的主要目的是为了缩减在嵌套循环连接中发生的随机读取量。排序合并适合在缺乏数据的选择性或者可用的索引时，或者两个源表都过于庞大(所选的数据超过表记录数的5%)时，排序合并连接将比嵌套循环连更加高效。
哈希连接哈希连接是在Oracle 7.3开始引入的，以代替排序合并及嵌套循环方式，提高效率。引入哈希连接，主要是为了解决嵌套循环连接中大量随机读取的问题，又要解决排序合并连接中排序代价过大的问题。在不需要排序的情况下，哈希函数就能够把连接对象集中在一起。可以哈希函数并不直接负责连接任务，而是负责把将要连接的对象提前集中在特定的位置。将具有相同哈希值的数据行集中存储在相同的空间上，该空间被称之为"分区"。在这些分区中必须要进行连接的两个分区成为"分区对"。实际上，哈希连接是以分区对为对象来执行连接操作的。在连接时，将较小分区中的数据行读入内存中并为其创建临时哈希表，然后将哈希表中的行视为内侧循环，较大分区中的行视为外侧循环来执行哈希连接。哈希连接适合于小结果集连接到大结果集的情况或者两个都为大结果集的情况，但存在先天限制就是仅适用于等值连接。
2)Oracle 测试示例
复制
-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 || 1 | NESTED LOOPS | | 1 | 46 | 3 (0)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 2 (0)| 00:00:01 ||* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 || 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 1 (0)| 00:00:01 ||* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |------------------------------------------------------------------------------------------ 内连接（排序合并）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON 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 | 449K| 18 (6)| 00:00:01 || 1 | MERGE JOIN | | 10000 | 449K| 18 (6)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 ||* 4 | SORT JOIN | | 10000 | 302K| 16 (7)| 00:00:01 || 5 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |------------------------------------------------------------------------------------------ 内连接（哈希连接）SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON 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 | 205K| 18 (6)| 00:00:01 ||* 1 | HASH JOIN | | 10000 | 205K| 18 (6)| 00:00:01 || 2 | VIEW | index$_join$_001 | 100 | 1000 | 3 (34)| 00:00:01 ||* 3 | HASH JOIN | | | | | || 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 || 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |---------------------------------------------------------------------------------------------- 左外连接（哈希外连接）SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;SQL> select * from table(dbms_xplan.display);--------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 205K| 18 (6)| 00:00:01 ||* 1 | HASH JOIN RIGHT OUTER | | 10000 | 205K| 18 (6)| 00:00:01 || 2 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 ||* 3 | HASH JOIN | | | | | || 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 || 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 || 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |---------------------------------------------------------------------------------------------- 全外连接（哈希全连接）SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;SQL> select * from table(dbms_xplan.display);---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 10000 | 673K| 18 (6)| 00:00:01 || 1 | VIEW | VW_FOJ_0 | 10000 | 673K| 18 (6)| 00:00:01 ||* 2 | HASH JOIN FULL OUTER | | 10000 | 205K| 18 (6)| 00:00:01 || 3 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 ||* 4 | HASH JOIN | | | | | || 5 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 || 6 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 || 7 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |----------------------------------------------------------------------------------------------- 半连接(排序合并半连接)SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 100 | 2300 | 18 (6)| 00:00:01 || 1 | MERGE JOIN SEMI | | 100 | 2300 | 18 (6)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 ||* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |------------------------------------------------------------------------------------------ 反连接(排序合并反连接)SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);SQL> select * from table(dbms_xplan.display);----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 || 1 | MERGE JOIN ANTI | | 1 | 23 | 18 (6)| 00:00:01 || 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 || 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 ||* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 ||* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |----------------------------------------------------------------------------------------SQL> alter table emp modify dept_id null;SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);SQL> select * from table(dbms_xplan.display);-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 || 1 | MERGE JOIN ANTI NA | | 1 | 23 | 18 (6)| 00:00:01 || 2 | SORT JOIN | | 100 | 1500 | 2 (0)| 00:00:01 || 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 || 4 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 ||* 5 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 ||* 6 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |-----------------------------------------------------------------------------------------* 设置为可没空后，优化器选择了MERGE JOIN ANTI NA explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\nSQL> select * from table(dbms_xplan.display);\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 46 | 3 (0)| 00:00:01 |\n| 1 | NESTED LOOPS | | 1 | 46 | 3 (0)| 00:00:01 |\n| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 31 | 2 (0)| 00:00:01 |\n|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |\n| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 1 (0)| 00:00:01 |\n|* 5 | INDEX UNIQUE SCAN | DEPT_PK | 1 | | 0 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n-- 内连接（排序合并）\nSQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\nSQL> select * from table(dbms_xplan.display);\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 10000 | 449K| 18 (6)| 00:00:01 |\n| 1 | MERGE JOIN | | 10000 | 449K| 18 (6)| 00:00:01 |\n| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |\n| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |\n|* 4 | SORT JOIN | | 10000 | 302K| 16 (7)| 00:00:01 |\n| 5 | TABLE ACCESS FULL | EMP | 10000 | 302K| 15 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n-- 内连接（哈希连接）\nSQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\nSQL> select * from table(dbms_xplan.display);\n--------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 10000 | 205K| 18 (6)| 00:00:01 |\n|* 1 | HASH JOIN | | 10000 | 205K| 18 (6)| 00:00:01 |\n| 2 | VIEW | index$_join$_001 | 100 | 1000 | 3 (34)| 00:00:01 |\n|* 3 | HASH JOIN | | | | | |\n| 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |\n| 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |\n| 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |\n--------------------------------------------------------------------------------------------\n-- 左外连接（哈希外连接）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\nSQL> select * from table(dbms_xplan.display);\n--------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n--------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 10000 | 205K| 18 (6)| 00:00:01 |\n|* 1 | HASH JOIN RIGHT OUTER | | 10000 | 205K| 18 (6)| 00:00:01 |\n| 2 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 |\n|* 3 | HASH JOIN | | | | | |\n| 4 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |\n| 5 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |\n| 6 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |\n--------------------------------------------------------------------------------------------\n-- 全外连接（哈希全连接）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\nSQL> select * from table(dbms_xplan.display);\n---------------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n---------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 10000 | 673K| 18 (6)| 00:00:01 |\n| 1 | VIEW | VW_FOJ_0 | 10000 | 673K| 18 (6)| 00:00:01 |\n|* 2 | HASH JOIN FULL OUTER | | 10000 | 205K| 18 (6)| 00:00:01 |\n| 3 | VIEW | index$_join$_002 | 100 | 1000 | 3 (34)| 00:00:01 |\n|* 4 | HASH JOIN | | | | | |\n| 5 | INDEX FAST FULL SCAN| DEPT_PK | 100 | 1000 | 1 (0)| 00:00:01 |\n| 6 | INDEX FAST FULL SCAN| IDX_DEPT_NAME | 100 | 1000 | 1 (0)| 00:00:01 |\n| 7 | TABLE ACCESS FULL | EMP | 10000 | 107K| 15 (0)| 00:00:01 |\n---------------------------------------------------------------------------------------------\n-- 半连接(排序合并半连接)\nSQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);\nSQL> select * from table(dbms_xplan.display);\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 100 | 2300 | 18 (6)| 00:00:01 |\n| 1 | MERGE JOIN SEMI | | 100 | 2300 | 18 (6)| 00:00:01 |\n| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |\n| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |\n|* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |\n|* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n-- 反连接(排序合并反连接)\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\nSQL> select * from table(dbms_xplan.display);\n----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 |\n| 1 | MERGE JOIN ANTI | | 1 | 23 | 18 (6)| 00:00:01 |\n| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |\n| 3 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |\n|* 4 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |\n|* 5 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n\nSQL> alter table emp modify dept_id null;\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\nSQL> select * from table(dbms_xplan.display);\n-----------------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-----------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | 23 | 18 (6)| 00:00:01 |\n| 1 | MERGE JOIN ANTI NA | | 1 | 23 | 18 (6)| 00:00:01 |\n| 2 | SORT JOIN | | 100 | 1500 | 2 (0)| 00:00:01 |\n| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1500 | 2 (0)| 00:00:01 |\n| 4 | INDEX FULL SCAN | DEPT_PK | 100 | | 1 (0)| 00:00:01 |\n|* 5 | SORT UNIQUE | | 4501 | 36008 | 16 (7)| 00:00:01 |\n|* 6 | TABLE ACCESS FULL | EMP | 4501 | 36008 | 15 (0)| 00:00:01 |\n-----------------------------------------------------------------------------------------\n* 设置为可没空后，优化器选择了MERGE JOIN ANTI NA"},"attribs":{"0":"*0|32+6zy*0+y"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"a61e7d7c-da3e-4298-8584-9c594b281d61","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":53,"type":"text","selection":{"start":10,"end":9114},"recordId":"doxcnRvIUISnab24NITS15ABV8d"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">1.
3. 国产库访问路径能力评测
下文将对国产数据库（含MySQL）做测试对比。在之前先看下结论，国产数据库在表关联能力方面尚可，MySQL在这方面差距相对较大。但国产数据库在优化策略制定上，明显比较单一，不如 Oracle 会根据不同语句制定有针对性的执行计划。此外，在同样架构、同等数据规模，都收集统计信息的情况下，国产库在不同连接方式下差异也很明显，特别是都不太考虑排序合并的方式。下表是针对四种连接方式，共计八条语句下各数据库的表现情况。如统一连接方式下执行计划相同，则只显示一次。
图片
1)MySQL
复制
-- 内连接（嵌套循环）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL || 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+-- 内连接（嵌套循环）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON 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 | 9796 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+-- 内连接（嵌套循环）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON 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 | 9796 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+-- 左外连接（嵌套循环）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.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 | 9796 | 100.00 | NULL || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+-- 全外连接（嵌套循环+哈希连接）mysql> select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near full outer join dept d on e.dept_id=d.dept_id at line 1* MySQL 中不支持full outer join，只能用下面的方式等价实现mysql> explain SELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_idUNION ALLSELECT e.emp_name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL;+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL| 1 | PRIMARY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL| 2 | UNION | d | NULL | index | NULL | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index| 2 | UNION | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 10.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+-- 半连接(嵌套循环+子查询物化)mysql> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | NULL || 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+-- 反连接(嵌套循环+子查询物化)mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10109 | 100.00 | Using where |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+mysql> alter table emp modify dept_id int null;mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where || 2 | SUBQUERY | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+* 退化为嵌套循环+子查询 explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |\n| 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n\n-- 内连接（嵌套循环）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n\n-- 内连接（嵌套循环）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n\n-- 左外连接（嵌套循环）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n\n-- 全外连接（嵌套循环+哈希连接）\nmysql> select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\nERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near full outer join dept d on e.dept_id=d.dept_id at line 1\n* MySQL 中不支持full outer join，只能用下面的方式等价实现\n\nmysql> explain \nSELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id\nUNION ALL\nSELECT e.emp_name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL;\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL\n| 1 | PRIMARY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL\n| 2 | UNION | d | NULL | index | NULL | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index\n| 2 | UNION | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 10.00 | Using where; Using join buffer (hash join) |\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n\n-- 半连接(嵌套循环+子查询物化)\nmysql> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL |\n| 1 | SIMPLE || NULL | eq_ref ||| 5 | testdb.dept.dept_id | 1 | 100.00 | NULL |\n| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n\n-- 反连接(嵌套循环+子查询物化)\nmysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |\n| 1 | SIMPLE || NULL | eq_ref ||| 5 | testdb.dept.dept_id | 1 | 100.00 | Using where; Not exists |\n| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10109 | 100.00 | Using where |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n\nmysql> alter table emp modify dept_id int null;\nmysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n| 1 | PRIMARY | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where |\n| 2 | SUBQUERY | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n* 退化为嵌套循环+子查询"},"attribs":{"0":"*0|2a+6x0*0+d"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"98aafdf4-705a-4942-b8ab-eee42dc09405","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":59,"type":"text","selection":{"start":0,"end":8977},"recordId":"doxcnZhpJOMqvU1HS7h73pvEPth"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class=""> explain select * from emp;\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+\n| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |\n+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+\n\n-- 索引扫描：索引单键扫描\nmysql> explain select * from emp where emp_id=111;\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n\n-- 索引扫描：索引范围扫描\nmysql> explain select * from emp where emp_idexplain select emp_name from emp;\n+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+\n| 1 | SIMPLE | emp | NULL | index | NULL | idx_emp_name | 33 | NULL | 9796 | 100.00 | Using index |\n+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+\n\n-- 索引扫描：索引跳跃扫描\nmysql> create table t as select * from information_schema.tables;\nmysql> insert into t select * from t;\n...\nmysql> insert into t select * from t;\nmysql> alter table t add id int;\nmysql> create index idx_tmp on t(table_type,table_name);\nmysql> analyze table t;\nmysql> explain select table_type,table_name from t where table_name=COLLATIONS;\n+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+\n| 1 | SIMPLE | t | NULL | range | idx_tmp | idx_tmp | 195 | NULL | 18045 | 100.00 | Using where; Using index for skip scan |\n+----+-------------+-------+------------+-------+---------------+---------+---------+------+-------+----------+----------------------------------------+\n"},"attribs":{"0":"*0|1a+2xs"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"348f36c5-f5e0-4fee-af11-51c0edaa496d","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":59,"type":"text","selection":{"start":0,"end":3808},"recordId":"XdAedcJs1ovcCzxftAlcG96jnyb"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（嵌套循环）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL || 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+-- 内连接（嵌套循环）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON 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 | 9796 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+-- 内连接（嵌套循环）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON 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 | 9796 | 100.00 | Using where || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+-- 左外连接（嵌套循环）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.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 | 9796 | 100.00 | NULL || 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+-- 全外连接（嵌套循环+哈希连接）mysql> select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near full outer join dept d on e.dept_id=d.dept_id at line 1* MySQL 中不支持full outer join，只能用下面的方式等价实现mysql> explain SELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_idUNION ALLSELECT e.emp_name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL;+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL| 1 | PRIMARY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL| 2 | UNION | d | NULL | index | NULL | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index| 2 | UNION | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 10.00 | Using where; Using join buffer (hash join) |+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+-- 半连接(嵌套循环+子查询物化)mysql> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | NULL || 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+-- 反连接(嵌套循环+子查询物化)mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL || 1 | SIMPLE | <subquery2> | NULL | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 5 | testdb.dept.dept_id | 1 | 100.00 | Using where; Not exists || 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10109 | 100.00 | Using where |+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+mysql> alter table emp modify dept_id int null;mysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where || 2 | SUBQUERY | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+* 退化为嵌套循环+子查询 explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n| 1 | SIMPLE | e | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |\n| 1 | SIMPLE | d | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+\n\n-- 内连接（嵌套循环）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n\n-- 内连接（嵌套循环）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | Using where |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+\n\n-- 左外连接（嵌套循环）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n| 1 | SIMPLE | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL |\n| 1 | SIMPLE | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL |\n+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+\n\n-- 全外连接（嵌套循环+哈希连接）\nmysql> select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\nERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near full outer join dept d on e.dept_id=d.dept_id at line 1\n* MySQL 中不支持full outer join，只能用下面的方式等价实现\n\nmysql> explain \nSELECT e.emp_name, d.dept_name FROM emp e LEFT JOIN dept d ON e.dept_id = d.dept_id\nUNION ALL\nSELECT e.emp_name, d.dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id = d.dept_id WHERE e.dept_id IS NULL;\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n| 1 | PRIMARY | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 100.00 | NULL\n| 1 | PRIMARY | d | NULL | eq_ref | PRIMARY | PRIMARY | 4 | testdb.e.dept_id | 1 | 100.00 | NULL\n| 2 | UNION | d | NULL | index | NULL | idx_dept_name | 103 | NULL | 100 | 100.00 | Using index\n| 2 | UNION | e | NULL | ALL | NULL | NULL | NULL | NULL | 9796 | 10.00 | Using where; Using join buffer (hash join) |\n+----+-------------+-------+------------+--------+---------------+---------------+---------+------------------+------+----------+--------------------------------------------+\n\n-- 半连接(嵌套循环+子查询物化)\nmysql> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n| 1 | SIMPLE | dept | NULL | ALL | PRIMARY | NULL | NULL | NULL | 100 | 100.00 | NULL |\n| 1 | SIMPLE || NULL | eq_ref ||| 5 | testdb.dept.dept_id | 1 | 100.00 | NULL |\n| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+------+----------+-------------+\n\n-- 反连接(嵌套循环+子查询物化)\nmysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n| 1 | SIMPLE | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |\n| 1 | SIMPLE || NULL | eq_ref ||| 5 | testdb.dept.dept_id | 1 | 100.00 | Using where; Not exists |\n| 2 | MATERIALIZED | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 10109 | 100.00 | Using where |\n+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+---------------------+-------+----------+-------------------------+\n\nmysql> alter table emp modify dept_id int null;\nmysql> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n| 1 | PRIMARY | dept | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | Using where |\n| 2 | SUBQUERY | emp | NULL | ALL | idx_emp_salary | NULL | NULL | NULL | 9796 | 51.31 | Using where |\n+----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+\n* 退化为嵌套循环+子查询"},"attribs":{"0":"*0|2a+6x0*0+d"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"98aafdf4-705a-4942-b8ab-eee42dc09405","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":59,"type":"text","selection":{"start":0,"end":8977},"recordId":"doxcnZhpJOMqvU1HS7h73pvEPth"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">1.2.
2)DM
复制
-- 内连接（哈希内连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;1 #NSET2: [1, 1, 277]2 #PRJT2: [1, 1, 277]; exp_num(8), is_atom(FALSE)3 #HASH2 INNER JOIN: [1, 1, 277]; RKEY_UNIQUE KEY_NUM(1); KEY(E.DEPT_ID=D.DEPT_ID) KEY_NULL_EQU(0)4 #NEST LOOP INDEX JOIN2: [1, 1, 277]5 #ACTRL: [1, 1, 277]6 #BLKUP2: [1, 1, 151]; INDEX33555485(E)7 #SSEK2: [1, 1, 151]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(111),exp_cast(111)], is_global(0)8 #BLKUP2: [1, 1, 30]; INDEX33555481(D)9 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[E.DEPT_ID,E.DEPT_ID], is_global(0)10 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)-- 内连接（哈希内连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;1 #NSET2: [3, 10000, 277]2 #PRJT2: [3, 10000, 277]; exp_num(8), is_atom(FALSE)3 #HASH2 INNER JOIN: [3, 10000, 277]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 151]; INDEX33555484(EMP as E); btr_scan(1)-- 内连接（哈希内连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;1 #NSET2: [2, 10000, 156]2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;1 #NSET2: [2, 10000, 78]2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)3 #HASH RIGHT JOIN2: [2, 10000, 78]; key_num(1), ret_null(0), KEY(D.DEPT_ID=E.DEPT_ID)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;1 #NSET2: [2, 10000, 78]2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)3 #HASH FULL JOIN2: [2, 10000, 78]; key_num(1), mix_aagr(0), mix_dist(0) KEY(D.DEPT_ID=E.DEPT_ID)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 半连接(哈希半连接)SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);1 #NSET2: [2, 100, 138]2 #PRJT2: [2, 100, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #SLCT2: [1, 4457, 60]; EMP.SALARY > var16 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)-- 反连接(哈希半连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);1 #NSET2: [2, 1, 138]2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193480.colname) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var17 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);1 #NSET2: [2, 1, 138]2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193500.colname) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var17 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n1 #NSET2: [1, 1, 277]\n2 #PRJT2: [1, 1, 277]; exp_num(8), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [1, 1, 277]; RKEY_UNIQUE KEY_NUM(1); KEY(E.DEPT_ID=D.DEPT_ID) KEY_NULL_EQU(0)\n4 #NEST LOOP INDEX JOIN2: [1, 1, 277]\n5 #ACTRL: [1, 1, 277]\n6 #BLKUP2: [1, 1, 151]; INDEX33555485(E)\n7 #SSEK2: [1, 1, 151]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(111),exp_cast(111)], is_global(0)\n8 #BLKUP2: [1, 1, 30]; INDEX33555481(D)\n9 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[E.DEPT_ID,E.DEPT_ID], is_global(0)\n10 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)\n\n-- 内连接（哈希内连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n1 #NSET2: [3, 10000, 277]\n2 #PRJT2: [3, 10000, 277]; exp_num(8), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [3, 10000, 277]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 151]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 内连接（哈希内连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n1 #NSET2: [2, 10000, 156]\n2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n1 #NSET2: [2, 10000, 78]\n2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)\n3 #HASH RIGHT JOIN2: [2, 10000, 78]; key_num(1), ret_null(0), KEY(D.DEPT_ID=E.DEPT_ID)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n1 #NSET2: [2, 10000, 78]\n2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)\n3 #HASH FULL JOIN2: [2, 10000, 78]; key_num(1), mix_aagr(0), mix_dist(0) KEY(D.DEPT_ID=E.DEPT_ID)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 半连接(哈希半连接)\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 100, 138]\n2 #PRJT2: [2, 100, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n6 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)\n\n-- 反连接(哈希半连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 1, 138]\n2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193480.colname) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)\n6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)\n\nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 1, 138]\n2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193500.colname) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)\n6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)"},"attribs":{"0":"*0|20+36u*0+1x"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"df6be6fe-6a56-48d4-83bf-739868cbc368","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":61,"type":"text","selection":{"start":0,"end":4203},"recordId":"doxcn4W1kJhADcDV9mWqfWENMvf"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class=""> explain select * from emp;\n1 #NSET2: [1, 10000, 163]\n2 #PRJT2: [1, 10000, 163]; exp_num(6), is_atom(FALSE)\n3 #CSCN2: [1, 10000, 163]; INDEX33555484(EMP); btr_scan(1)\n\n-- 索引扫描：索引唯一扫描\nSQL> explain select * from emp where emp_id=111;\n1 #NSET2: [1, 1, 163]\n2 #PRJT2: [1, 1, 163]; exp_num(6), is_atom(FALSE)\n3 #BLKUP2: [1, 1, 163]; INDEX33555485(EMP)\n4 #SSEK2: [1, 1, 163]; scan_type(ASC), INDEX33555485(EMP), scan_range[exp_cast(111),exp_cast(111)], is_global(0)\n* 没有唯一扫描方式，DM都认为是范围扫描\n\n-- 索引扫描：索引范围扫描\nSQL> explain select * from emp where emp_idexplain select emp_name from emp;\n1 #NSET2: [1, 10000, 60]\n2 #PRJT2: [1, 10000, 60]; exp_num(2), is_atom(FALSE)\n3 #SSCN: [1, 10000, 60]; IDX_EMP_NAME(EMP); btr_scan(1); is_global(0)\n* 直接使用索引扫描，不用再回表查\n\n-- 索引扫描：索引跳跃扫描（index skip scan）\nSQL> create table t as select 1 id,object_name from dba_objects;\nSQL> insert into t select 2 ,object_name from dba_objects;\nSQL> insert into t select 3 ,object_name from dba_objects;\nSQL> insert into t select 4 ,object_name from dba_objects;\nSQL> create index idx_t on t(id,object_name);\nSQL> CALL SP_TAB_INDEX_STAT_INIT (TESTUSER, T);\nSQL> explain select * from t where object_name=TEST;\n1 #NSET2: [1, 109, 64]\n2 #PRJT2: [1, 109, 64]; exp_num(3), is_atom(FALSE)\n3 #SLCT2: [1, 109, 64]; T.OBJECT_NAME = TEST\n4 #SSCN: [1, 109, 64]; IDX_T(T); btr_scan(1); is_global(0)\n* 直接使用索引扫描，实现了跳跃扫描功能"},"attribs":{"0":"*0|15+1bt*0+k"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"0a35004b-6907-4360-87a7-21f2865d269f","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":61,"type":"text","selection":{"start":0,"end":1741},"recordId":"OryBdxBRuos3ynxXOlrc0OEFnbd"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（哈希内连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;1 #NSET2: [1, 1, 277]2 #PRJT2: [1, 1, 277]; exp_num(8), is_atom(FALSE)3 #HASH2 INNER JOIN: [1, 1, 277]; RKEY_UNIQUE KEY_NUM(1); KEY(E.DEPT_ID=D.DEPT_ID) KEY_NULL_EQU(0)4 #NEST LOOP INDEX JOIN2: [1, 1, 277]5 #ACTRL: [1, 1, 277]6 #BLKUP2: [1, 1, 151]; INDEX33555485(E)7 #SSEK2: [1, 1, 151]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(111),exp_cast(111)], is_global(0)8 #BLKUP2: [1, 1, 30]; INDEX33555481(D)9 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[E.DEPT_ID,E.DEPT_ID], is_global(0)10 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)-- 内连接（哈希内连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;1 #NSET2: [3, 10000, 277]2 #PRJT2: [3, 10000, 277]; exp_num(8), is_atom(FALSE)3 #HASH2 INNER JOIN: [3, 10000, 277]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 151]; INDEX33555484(EMP as E); btr_scan(1)-- 内连接（哈希内连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;1 #NSET2: [2, 10000, 156]2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;1 #NSET2: [2, 10000, 78]2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)3 #HASH RIGHT JOIN2: [2, 10000, 78]; key_num(1), ret_null(0), KEY(D.DEPT_ID=E.DEPT_ID)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;1 #NSET2: [2, 10000, 78]2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)3 #HASH FULL JOIN2: [2, 10000, 78]; key_num(1), mix_aagr(0), mix_dist(0) KEY(D.DEPT_ID=E.DEPT_ID)4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)-- 半连接(哈希半连接)SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);1 #NSET2: [2, 100, 138]2 #PRJT2: [2, 100, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #SLCT2: [1, 4457, 60]; EMP.SALARY > var16 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)-- 反连接(哈希半连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);1 #NSET2: [2, 1, 138]2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193480.colname) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var17 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);1 #NSET2: [2, 1, 138]2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193500.colname) KEY_NULL_EQU(0)4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var17 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n1 #NSET2: [1, 1, 277]\n2 #PRJT2: [1, 1, 277]; exp_num(8), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [1, 1, 277]; RKEY_UNIQUE KEY_NUM(1); KEY(E.DEPT_ID=D.DEPT_ID) KEY_NULL_EQU(0)\n4 #NEST LOOP INDEX JOIN2: [1, 1, 277]\n5 #ACTRL: [1, 1, 277]\n6 #BLKUP2: [1, 1, 151]; INDEX33555485(E)\n7 #SSEK2: [1, 1, 151]; scan_type(ASC), INDEX33555485(EMP as E), scan_range[exp_cast(111),exp_cast(111)], is_global(0)\n8 #BLKUP2: [1, 1, 30]; INDEX33555481(D)\n9 #SSEK2: [1, 1, 30]; scan_type(ASC), INDEX33555481(DEPT as D), scan_range[E.DEPT_ID,E.DEPT_ID], is_global(0)\n10 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)\n\n-- 内连接（哈希内连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n1 #NSET2: [3, 10000, 277]\n2 #PRJT2: [3, 10000, 277]; exp_num(8), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [3, 10000, 277]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 126]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 151]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 内连接（哈希内连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n1 #NSET2: [2, 10000, 156]\n2 #PRJT2: [2, 10000, 156]; exp_num(2), is_atom(FALSE)\n3 #HASH2 INNER JOIN: [2, 10000, 156]; LKEY_UNIQUE KEY_NUM(1); KEY(D.DEPT_ID=E.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n1 #NSET2: [2, 10000, 78]\n2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)\n3 #HASH RIGHT JOIN2: [2, 10000, 78]; key_num(1), ret_null(0), KEY(D.DEPT_ID=E.DEPT_ID)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n1 #NSET2: [2, 10000, 78]\n2 #PRJT2: [2, 10000, 78]; exp_num(2), is_atom(FALSE)\n3 #HASH FULL JOIN2: [2, 10000, 78]; key_num(1), mix_aagr(0), mix_dist(0) KEY(D.DEPT_ID=E.DEPT_ID)\n4 #CSCN2: [1, 100, 78]; INDEX33555480(DEPT as D); btr_scan(1)\n5 #CSCN2: [1, 10000, 78]; INDEX33555484(EMP as E); btr_scan(1)\n\n-- 半连接(哈希半连接)\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 100, 138]\n2 #PRJT2: [2, 100, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 100, 138]; KEY_NUM(1); KEY(DEPT.DEPT_ID=EMP.DEPT_ID) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n6 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)\n\n-- 反连接(哈希半连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 1, 138]\n2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193480.colname) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)\n6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)\n\nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n1 #NSET2: [2, 1, 138]\n2 #PRJT2: [2, 1, 138]; exp_num(4), is_atom(FALSE)\n3 #HASH LEFT SEMI JOIN2: [2, 1, 138]; (ANTI),KEY_NUM(1); KEY(DEPT.DEPT_ID=DMTEMPVIEW_889193500.colname) KEY_NULL_EQU(0)\n4 #CSCN2: [1, 100, 138]; INDEX33555480(DEPT); btr_scan(1)\n5 #PRJT2: [1, 4457, 60]; exp_num(1), is_atom(FALSE)\n6 #SLCT2: [1, 4457, 60]; EMP.SALARY > var1\n7 #CSCN2: [1, 10000, 60]; INDEX33555484(EMP); btr_scan(1)"},"attribs":{"0":"*0|20+36u*0+1x"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"df6be6fe-6a56-48d4-83bf-739868cbc368","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":61,"type":"text","selection":{"start":0,"end":4203},"recordId":"doxcn4W1kJhADcDV9mWqfWENMvf"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">1.2.
3)KingBase
复制
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.46..10.73 rows=1 width=71) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29) -> Hash (cost=8.44..8.44 rows=1 width=42) -> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42) Index Cond: (emp_id = 111::numeric)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=71) 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=29) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 半连接(嵌套循环半连接)SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------------------- Result (cost=16384.43..40319.75 rows=1 width=29) One-Time Filter: (NOT $1) InitPlan 1 (returns $1) -> Gather (cost=1000.00..16384.43 rows=1 width=0) Workers Planned: 2 -> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0) Filter: ((dept_id IS NULL) AND (salary > 1500::double precision)) -> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n QUERY PLAN\n---------------------------------------------------------------------------------\n Hash Join (cost=8.46..10.73 rows=1 width=71)\n Hash Cond: (d.dept_id = e.dept_id)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)\n -> Hash (cost=8.44..8.44 rows=1 width=42)\n -> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42)\n Index Cond: (emp_id = 111::numeric)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Join (cost=3.25..22914.40 rows=990099 width=71)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=42)\n -> Hash (cost=2.00..2.00 rows=100 width=29)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Join (cost=3.25..22914.40 rows=990099 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 半连接(嵌套循环半连接)\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------\n Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)\n \n-- 反连接(排序合并反连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------\n Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)\n \nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n-------------------------------------------------------------------------------------\n Result (cost=16384.43..40319.75 rows=1 width=29)\n One-Time Filter: (NOT $1)\n InitPlan 1 (returns $1)\n -> Gather (cost=1000.00..16384.43 rows=1 width=0)\n Workers Planned: 2\n -> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0)\n Filter: ((dept_id IS NULL) AND (salary > 1500::double precision))\n -> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)"},"attribs":{"0":"*0|2e+3j8*0+1m"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"9be58d40-70ac-4711-8d20-029e403f7e33","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":63,"type":"text","selection":{"start":0,"end":4638},"recordId":"doxcnwTyRX2j4gx9aTIhOM7SBsg"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.46..10.73 rows=1 width=71) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29) -> Hash (cost=8.44..8.44 rows=1 width=42) -> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42) Index Cond: (emp_id = 111::numeric)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=71) 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=29) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 半连接(嵌套循环半连接)SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------------------- Result (cost=16384.43..40319.75 rows=1 width=29) One-Time Filter: (NOT $1) InitPlan 1 (returns $1) -> Gather (cost=1000.00..16384.43 rows=1 width=0) Workers Planned: 2 -> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0) Filter: ((dept_id IS NULL) AND (salary > 1500::double precision)) -> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n QUERY PLAN\n---------------------------------------------------------------------------------\n Hash Join (cost=8.46..10.73 rows=1 width=71)\n Hash Cond: (d.dept_id = e.dept_id)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)\n -> Hash (cost=8.44..8.44 rows=1 width=42)\n -> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42)\n Index Cond: (emp_id = 111::numeric)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Join (cost=3.25..22914.40 rows=990099 width=71)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=42)\n -> Hash (cost=2.00..2.00 rows=100 width=29)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Join (cost=3.25..22914.40 rows=990099 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n----------------------------------------------------------------------\n Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=18)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)\n \n-- 半连接(嵌套循环半连接)\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------\n Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)\n \n-- 反连接(排序合并反连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------\n Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)\n \nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n-------------------------------------------------------------------------------------\n Result (cost=16384.43..40319.75 rows=1 width=29)\n One-Time Filter: (NOT $1)\n InitPlan 1 (returns $1)\n -> Gather (cost=1000.00..16384.43 rows=1 width=0)\n Workers Planned: 2\n -> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0)\n Filter: ((dept_id IS NULL) AND (salary > 1500::double precision))\n -> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29)\n Join Filter: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29)\n -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5)\n Filter: (salary > 1500::double precision)"},"attribs":{"0":"*0|2e+3j8*0+1m"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"9be58d40-70ac-4711-8d20-029e403f7e33","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":63,"type":"text","selection":{"start":0,"end":4638},"recordId":"doxcnwTyRX2j4gx9aTIhOM7SBsg"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.46..10.73 rows=1 width=71) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29) -> Hash (cost=8.44..8.44 rows=1 width=42) -> Index Scan using EMP_PK on emp e (cost=0.42..8.44 rows=1 width=42) Index Cond: (emp_id = 111::numeric)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=71) 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=29) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=29)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Join (cost=3.25..22914.40 rows=990099 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Left Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN---------------------------------------------------------------------- Hash Full Join (cost=3.25..22914.40 rows=1000000 width=28) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..20176.00 rows=1000000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=18) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=18)
-- 半连接(嵌套循环半连接)SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Semi Join (cost=0.00..23935.31 rows=100 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..23935.31 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------------------- Result (cost=16384.43..40319.75 rows=1 width=29) One-Time Filter: (NOT $1) InitPlan 1 (returns $1) -> Gather (cost=1000.00..16384.43 rows=1 width=0) Workers Planned: 2 -> Parallel Seq Scan on emp emp_1 (cost=0.00..15384.33 rows=1 width=0) Filter: ((dept_id IS NULL) AND (salary > 1500::double precision)) -> Nested Loop Anti RSNA Join (cost=16384.43..40319.75 rows=1 width=29) Join Filter: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=29) -> Seq Scan on emp (cost=0.00..22676.00 rows=451176 width=5) Filter: (salary > 1500::double precision)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.
4)YashanDB
复制
-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| ||* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 左外连接（嵌套循环）SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 全外连接（哈希全连接）SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 半连接(嵌套循环)SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| || 2 | HASH DISTINCT | | | 50| 37( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 反连接(排序合并反连接)SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+SQL> alter table emp modify dept_id null;SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |\n|* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |\n| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 内连接（嵌套循环）\nSQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 内连接（嵌套循环）\nSQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 左外连接（嵌套循环）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 全外连接（哈希全连接）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 半连接(嵌套循环)\nSQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| |\n| 2 | HASH DISTINCT | | | 50| 37( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 反连接(排序合并反连接)\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\nSQL> alter table emp modify dept_id null;\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n"},"attribs":{"0":"*0|2p+8eo"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"f901b47a-6139-4b41-9545-a5b41c9aa7f8","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":65,"type":"text","selection":{"start":0,"end":10896},"recordId":"doxcnv3JaP9om3xrKAewxHuXTGd"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class=""> explain plan for select * from emp;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 41( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 索引扫描：索引唯一扫描（index unique scan）\nSQL> explain select * from emp where emp_id=111;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |\n|* 2 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 索引扫描：索引范围扫描（index range scan）\nSQL> explain select * from emp where emp_idexplain select emp_name from emp;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | INDEX FAST FULL SCAN | IDX_EMP_NAME | TESTUSER | 10000| 29( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 索引扫描：索引全扫描（index full scan）\nexec dbms_stats.set_table_stats(TESTUSER,EMP,null,1000000,5000,34);\n//通过伪造统计信息，放大表扫描的成本，让优化器选择使用索引全扫描\n\nSQL> explain select emp_name from emp order by 1;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | INDEX FULL SCAN | IDX_EMP_NAME | TESTUSER | 1000000| 29( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 索引扫描：索引跳跃扫描（index skip scan）\nSQL> create table t as select 1 id,object_name from dba_objects;\nSQL> insert into t select 2 ,object_name from dba_objects;\n...\nSQL> create index idx_t on t(id,object_name);\nSQL> exec dbms_stats.gather_table_stats(user,t,cascade=>true);\nSQL> explain select * from t where object_name=TEST;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | INDEX SKIP SCAN | IDX_T | TESTUSER | 1| 3( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+"},"attribs":{"0":"*0|1q+4gc*0+3p"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"d2dfb047-eb89-436b-819e-04e0c8a67670","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":65,"type":"text","selection":{"start":0,"end":5905},"recordId":"FnsodDg9yoQkEkxUkHicvo7pnGb"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| ||* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 左外连接（嵌套循环）SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 全外连接（哈希全连接）SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 半连接(嵌套循环)SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| || 2 | HASH DISTINCT | | | 50| 37( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 反连接(排序合并反连接)SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+SQL> alter table emp modify dept_id null;SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| |\n|* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| |\n| 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 内连接（嵌套循环）\nSQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 内连接（嵌套循环）\nSQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 左外连接（嵌套循环）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 全外连接（哈希全连接）\nSQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| |\n| 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| |\n| 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 半连接(嵌套循环)\nSQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n| 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| |\n| 2 | HASH DISTINCT | | | 50| 37( 0)| |\n| 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| |\n|* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\n-- 反连接(排序合并反连接)\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n\nSQL> alter table emp modify dept_id null;\nSQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n| 0 | SELECT STATEMENT | | | | | |\n|* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| |\n| 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| |\n|* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| |\n| 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |\n+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+\n"},"attribs":{"0":"*0|2p+8eo"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"f901b47a-6139-4b41-9545-a5b41c9aa7f8","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":65,"type":"text","selection":{"start":0,"end":10896},"recordId":"doxcnv3JaP9om3xrKAewxHuXTGd"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 1| 1( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 1| 1( 0)| ||* 3 | INDEX UNIQUE SCAN | EMP_PK | TESTUSER | 1| 1( 0)| || 4 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 5 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 内连接（嵌套循环）SQL> explain plan for SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 左外连接（嵌套循环）SQL> explain plan for select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS LEFT OUTER | | | 10000| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 4 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 全外连接（哈希全连接）SQL> explain plan for select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN FULL OUTER | | | 19900| 48( 0)| || 2 | TABLE ACCESS FULL | EMP | TESTUSER | 10000| 46( 0)| || 3 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 半连接(嵌套循环)SQL> explain plan for select * from dept where dept_id in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | || 1 | NESTED INDEX LOOPS INNER | | | 50| 37( 0)| || 2 | HASH DISTINCT | | | 50| 37( 0)| || 3 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 4 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 5 | TABLE ACCESS BY INDEX ROWID | DEPT | TESTUSER | 1| 1( 0)| ||* 6 | INDEX UNIQUE SCAN | DEPT_PK | TESTUSER | 1| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+-- 反连接(排序合并反连接)SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+SQL> alter table emp modify dept_id null;SQL> explain plan for select * from dept where dept_id not in (select dept_id from emp where salary>1500);+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 0 | SELECT STATEMENT | | | | | ||* 1 | HASH JOIN RIGHT ANTI NA | | | 50| 38( 0)| || 2 | TABLE ACCESS BY INDEX ROWID | EMP | TESTUSER | 4491| 36( 0)| ||* 3 | INDEX RANGE SCAN | IDX_EMP_SALARY | TESTUSER | 4491| 12( 0)| || 4 | TABLE ACCESS FULL | DEPT | TESTUSER | 100| 1( 0)| |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+1.2.3.4.5.
5)Vastbase
复制
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.28..10.69 rows=1 width=74) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=8.27..8.27 rows=1 width=44) -> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44) Index Cond: (emp_id = 111::number)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=74) 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=30) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Left Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Full Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 半连接(哈希连接）SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN-------------------------------------------------------------------------- Hash Join (cost=255.20..257.91 rows=100 width=30) Hash Cond: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=254.63..254.63 rows=46 width=8) -> HashAggregate (cost=254.17..254.63 rows=46 width=8) Group By Key: emp.dept_id -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------- Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30) Hash Cond: (emp.dept_id = dept.dept_id) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision) -> Hash (cost=2.00..2.00 rows=100 width=30) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30) Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL)) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Materialize (cost=0.00..265.33 rows=4467 width=8) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n QUERY PLAN\n---------------------------------------------------------------------------------\n Hash Join (cost=8.28..10.69 rows=1 width=74)\n Hash Cond: (d.dept_id = e.dept_id)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)\n -> Hash (cost=8.27..8.27 rows=1 width=44)\n -> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44)\n Index Cond: (emp_id = 111::number)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Join (cost=3.25..351.51 rows=9901 width=74)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44)\n -> Hash (cost=2.00..2.00 rows=100 width=30)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Join (cost=3.25..351.51 rows=9901 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Left Join (cost=3.25..351.51 rows=10000 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Full Join (cost=3.25..351.51 rows=10000 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 半连接(哈希连接）\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n QUERY PLAN\n--------------------------------------------------------------------------\n Hash Join (cost=255.20..257.91 rows=100 width=30)\n Hash Cond: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)\n -> Hash (cost=254.63..254.63 rows=46 width=8)\n -> HashAggregate (cost=254.17..254.63 rows=46 width=8)\n Group By Key: emp.dept_id\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)\n \n-- 反连接(排序合并反连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n-------------------------------------------------------------------\n Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30)\n Hash Cond: (emp.dept_id = dept.dept_id)\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)\n -> Hash (cost=2.00..2.00 rows=100 width=30)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) \n \nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------------\n Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30)\n Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL))\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)\n -> Materialize (cost=0.00..265.33 rows=4467 width=8)\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)"},"attribs":{"0":"*0|2c+3ez*0+1k"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"db5fe344-4539-4c19-8a14-20d9ed7d49ea","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":67,"type":"text","selection":{"start":0,"end":4483},"recordId":"doxcnT1YOgYZcxczQl3hooSi6Kb"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class=""> explain select * from emp;\n QUERY PLAN\n----------------------------------------------------------\n Seq Scan on emp (cost=0.00..218.00 rows=10000 width=44)\n* 表的顺序扫描\n\n-- 索引扫描：索引唯一扫描\nvastbase=> explain select * from emp where emp_id=111;\n QUERY PLAN\n-------------------------------------------------------------------\n Index Scan using emp_pk on emp (cost=0.00..8.27 rows=1 width=44)\n Index Cond: (emp_id = 111::number)\n* 标准索引扫描\n \n-- 索引扫描：索引范围扫描\nvastbase=> explain select * from emp where emp_idexplain select emp_name from emp;\n QUERY PLAN\n----------------------------------------------------------\n Seq Scan on emp (cost=0.00..218.00 rows=10000 width=12)\n* 默认走了全表扫描(即使增加到100万的记录也是如此)\n\nvastbase=> explain analyze verbose select /*+IndexOnlyScan(emp idx_emp_name)*/ emp_name from emp;\n QUERY PLAN\n-------------------------------------------------------------------------------------------------------------------------------------------\n Index Only Scan using idx_emp_name on testuser.emp (cost=0.00..671.02 rows=10000 width=12) (actual time=0.610..5.090 rows=10000 loops=1)\n Output: emp_name\n Heap Fetches: 10000\n Total runtime: 5.580 ms\n * 尝试强制走索引扫描，成本更高。 \n\n-- 索引扫描：索引跳跃扫描（index skip scan）\nvastbase=> create table t as select 1 id,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;\nvastbase=> insert into t select 2 ,attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff, atttypmod from pg_attribute;\n...\nvastbase=> create index idx_t on t(id,attname );\nvastbase=> analyze verbose t;\nvastbase=> explain select * from t where attname =TEST;\n QUERY PLAN\n---------------------------------------------------------------------\n Index Scan using idx_t on t (cost=0.00..1331.40 rows=13 width=156)\n Index Cond: (attname = TEST::name)\n* 直接使用索引扫描，实现了跳跃扫描功能"},"attribs":{"0":"*0|1e+1qs*0+k"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"bfc867cc-a6c9-4e47-ae01-070d00d63431","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":67,"type":"text","selection":{"start":0,"end":2280},"recordId":"DXbGd8Dmxox0N7x4eREc6d0ynbg"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.28..10.69 rows=1 width=74) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=8.27..8.27 rows=1 width=44) -> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44) Index Cond: (emp_id = 111::number)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=74) 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=30) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Left Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Full Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 半连接(哈希连接）SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN-------------------------------------------------------------------------- Hash Join (cost=255.20..257.91 rows=100 width=30) Hash Cond: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=254.63..254.63 rows=46 width=8) -> HashAggregate (cost=254.17..254.63 rows=46 width=8) Group By Key: emp.dept_id -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------- Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30) Hash Cond: (emp.dept_id = dept.dept_id) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision) -> Hash (cost=2.00..2.00 rows=100 width=30) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30) Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL)) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Materialize (cost=0.00..265.33 rows=4467 width=8) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision) explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111;\n QUERY PLAN\n---------------------------------------------------------------------------------\n Hash Join (cost=8.28..10.69 rows=1 width=74)\n Hash Cond: (d.dept_id = e.dept_id)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)\n -> Hash (cost=8.27..8.27 rows=1 width=44)\n -> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44)\n Index Cond: (emp_id = 111::number)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Join (cost=3.25..351.51 rows=9901 width=74)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=44)\n -> Hash (cost=2.00..2.00 rows=100 width=30)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)\n \n-- 内连接（哈希连接）\nSQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Join (cost=3.25..351.51 rows=9901 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 左外连接（哈希外连接）\nSQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Left Join (cost=3.25..351.51 rows=10000 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 全外连接（哈希全连接）\nSQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id;\n QUERY PLAN\n---------------------------------------------------------------------\n Hash Full Join (cost=3.25..351.51 rows=10000 width=24)\n Hash Cond: (e.dept_id = d.dept_id)\n -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20)\n -> Hash (cost=2.00..2.00 rows=100 width=20)\n -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)\n \n-- 半连接(哈希连接）\nSQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500);\n QUERY PLAN\n--------------------------------------------------------------------------\n Hash Join (cost=255.20..257.91 rows=100 width=30)\n Hash Cond: (dept.dept_id = emp.dept_id)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)\n -> Hash (cost=254.63..254.63 rows=46 width=8)\n -> HashAggregate (cost=254.17..254.63 rows=46 width=8)\n Group By Key: emp.dept_id\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)\n \n-- 反连接(排序合并反连接)\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n-------------------------------------------------------------------\n Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30)\n Hash Cond: (emp.dept_id = dept.dept_id)\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)\n -> Hash (cost=2.00..2.00 rows=100 width=30)\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) \n \nSQL> alter table emp modify dept_id null;\nSQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500);\n QUERY PLAN\n------------------------------------------------------------------------\n Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30)\n Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL))\n -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)\n -> Materialize (cost=0.00..265.33 rows=4467 width=8)\n -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8)\n Filter: (salary > 1500::double precision)"},"attribs":{"0":"*0|2c+3ez*0+1k"}},"apool":{"numToAttrib":{"0":["author","6932737685563949084"]},"nextNum":1}},"type":"text","referenceRecordMap":{},"extra":{"channel":"saas","pasteRandomId":"db5fe344-4539-4c19-8a14-20d9ed7d49ea","mention_page_title":{},"external_mention_url":{}},"isKeepQuoteContainer":false,"isFromCode":true,"selection":[{"id":67,"type":"text","selection":{"start":0,"end":4483},"recordId":"doxcnT1YOgYZcxczQl3hooSi6Kb"}],"payloadMap":{},"isCut":false}" data-lark-record-format="docx/text" class="">
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id WHERE e.emp_id = 111; QUERY PLAN--------------------------------------------------------------------------------- Hash Join (cost=8.28..10.69 rows=1 width=74) Hash Cond: (d.dept_id = e.dept_id) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=8.27..8.27 rows=1 width=44) -> Index Scan using emp_pk on emp e (cost=0.00..8.27 rows=1 width=44) Index Cond: (emp_id = 111::number)
-- 内连接（哈希连接）SQL> explain SELECT d.*, e.* FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=74) 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=30) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=30)
-- 内连接（哈希连接）SQL> explain SELECT d.dept_name, e.emp_name FROM dept d JOIN emp e ON d.dept_id = e.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Join (cost=3.25..351.51 rows=9901 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 左外连接（哈希外连接）SQL> explain select e.emp_name,d.dept_name from emp e left outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Left Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 全外连接（哈希全连接）SQL> explain select e.emp_name,d.dept_name from emp e full outer join dept d on e.dept_id=d.dept_id; QUERY PLAN--------------------------------------------------------------------- Hash Full Join (cost=3.25..351.51 rows=10000 width=24) Hash Cond: (e.dept_id = d.dept_id) -> Seq Scan on emp e (cost=0.00..218.00 rows=10000 width=20) -> Hash (cost=2.00..2.00 rows=100 width=20) -> Seq Scan on dept d (cost=0.00..2.00 rows=100 width=20)
-- 半连接(哈希连接）SQL> explain select * from dept where dept_id in (select dept_id from emp where salary>1500); QUERY PLAN-------------------------------------------------------------------------- Hash Join (cost=255.20..257.91 rows=100 width=30) Hash Cond: (dept.dept_id = emp.dept_id) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Hash (cost=254.63..254.63 rows=46 width=8) -> HashAggregate (cost=254.17..254.63 rows=46 width=8) Group By Key: emp.dept_id -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision)
-- 反连接(排序合并反连接)SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------- Hash Right Anti Join (cost=3.25..261.08 rows=1 width=30) Hash Cond: (emp.dept_id = dept.dept_id) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision) -> Hash (cost=2.00..2.00 rows=100 width=30) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30)
SQL> alter table emp modify dept_id null;SQL> explain select * from dept where dept_id not in (select dept_id from emp where salary>1500); QUERY PLAN------------------------------------------------------------------------ Nested Loop Anti Join (cost=0.00..612.03 rows=1 width=30) Join Filter: ((dept.dept_id = emp.dept_id) OR (emp.dept_id IS NULL)) -> Seq Scan on dept (cost=0.00..2.00 rows=100 width=30) -> Materialize (cost=0.00..265.33 rows=4467 width=8) -> Seq Scan on emp (cost=0.00..243.00 rows=4467 width=8) Filter: (salary > 1500::double precision)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.
THE END
