关于oracle:表连接方法

14次阅读

共计 14951 个字符，预计需要花费 38 分钟才能阅读完成。

Oracle 表连贯办法有四种：

  • 排序合并连贯（Sort Merge Join）
  • 嵌套循环连贯（Nested Loops Join）
  • 哈希连贯（Hash Join）
  • 笛卡尔积（Cartesian Product）

排序合并连贯（Sort Merge Join）

排序合并连贯是将连贯的两个表应用连贯列排序后，对排序后的后果集进行合并后再失去匹配记录。如果连贯列下面有索引，能够防止排序，那么优化器就有可能会抉择排序合并连贯。能够用于 =，>，>=，<，<= 连贯条件，不适用于 <>，like 连贯条件。对应执行打算为 SORT JOIN 和MERGE JOIN。

SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id;

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

-- 如果没有索引，则会抉择哈希连贯：SQL> create table emp as select * from employees;

Table created.

SQL> create table dept as select * from departments;

Table created.

SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   106 |  5936 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   106 |  5936 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    27 |   810 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |   107 |  2782 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

-- 如果应用不等连贯条件，则会抉择嵌套循环连贯：SQL> select e.employee_id,d.department_name from employees e,departments d where e.department_id!=d.department_id;

2756 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2968905875

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |  2757 | 63411 |    41   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |             |  2757 | 63411 |    41   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES   |   102 |   714 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

嵌套循环连贯（Nested Loops Join）

嵌套循环连贯是 Oracle 将连贯的两个表依据后果集的大小，决定出驱动表和被驱动表。后果集小的作为驱动表，后果集大的作为被驱动表。对于驱动表的每一行，都要与被驱动表的所有行应用连贯条件进行匹配。实用于驱动表后果集很小，被驱动表在连贯列上有高效索引的表连贯。能够用于所有连贯条件。对应的执行打算为NESTED LOOPS。

SQL> select e.employee_id,e.last_name,d.department_name from employees e,departments d where e.department_id=d.department_id and d.department_id=80;

34 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1492013603

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    34 |  1054 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |             |    34 |  1054 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS FULL          | EMPLOYEES   |    34 |   510 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

-- 这里 DEPARTMENTS 表加了条件过滤后后果集只有 1 行，所以被选为驱动表。SQL> select e.first_name,e.last_name,e.salary,d.department_name from employees e,departments d where d.department_name IN ('Marketing', 'Sales') and e.department_id = d.department_id;

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |    19 |   722 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |                   |    20 |   722 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     2 |    32 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   220 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

这里有 2 次 NESTED LOOPS，是因为 Oracle 11g 引入了引入了向量 I /O（Vector I/O），批量解决多个物理 I / O 申请来进步嵌套循环连贯的效率。

如果驱动表的限度条件的字段上有索引，被驱动表的连贯条件的字段上有索引，则应用嵌套循环连贯的效率就会很高。

哈希连贯（Hash Join）

如果两个表对应的后果集很大，应用排序合并的话，排序操作老本较高；应用嵌套循环的话，则循环次数又很多，须要屡次拜访被驱动表的后果集；为了进步这种状况下表连贯的效率，优化器提供了新的表连贯办法，即哈希连贯。哈希连贯是应用哈希运算来失去后果的表连贯办法。只实用于等值连贯条件。

SQL> create table emp as select * from employees;

Table created.

SQL> create table dept as select * from departments;

Table created.

SQL> select e.employee_id,d.department_name from emp e,dept d where e.department_id=d.department_id;

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   106 |  5936 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN         |      |   106 |  5936 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    27 |   810 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |   107 |  2782 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

笛卡尔积（Cartesian Product）

如果两个表做表连贯而没有连贯条件，就会产生笛卡尔积，在理论工作中应该尽可能防止笛卡尔积。笛卡尔积对应的执行打算中有关键字MERGE JOIN CARTESIAN。

SQL> select last_name,department_name from emp,dept;

2889 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2034389985

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  2889 | 57780 |    41   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |  2889 | 57780 |    41   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | DEPT |    27 |   324 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |   107 |   856 |    38   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | EMP  |   107 |   856 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

其余

外连贯

后面连贯办法的示例都是内连贯，咱们晓得 Oracle 还有外连贯，包含左外连贯，右外连贯和全外连贯。执行打算中左外连贯和右外连贯对应的关键字是OUTER，全外连贯对应的关键字是FULL OUTER。

-- 内连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e join departments d on e.department_id=d.department_id;

106 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1343509718

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

-- 左外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e left join departments d on e.department_id=d.department_id;

107 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2296652067

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   107 |  3317 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |             |   107 |  3317 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

-- 右外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e right join departments d on e.department_id=d.department_id;

122 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 514479674

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |             |   106 |  3286 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

-- 全外连贯：SQL> select e.employee_id,e.last_name,d.department_name from employees e full join departments d on e.department_id=d.department_id;

123 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2631508678

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |   122 |  5368 |     6   (0)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0    |   122 |  5368 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|             |   122 |  3782 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

半连贯

如果 where 条件有 exists、in 或 =any 操作符 + 子查问，则 Oracle 会将其解决为半连贯，执行打算中对应的关键字为SEMI。

SQL> select department_id,department_name from departments d where exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id=any(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2188966913

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI             |             |    10 |   230 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

反连贯

如果 where 条件有 not exists、not in 或 <>all 操作符 + 子查问，则 Oracle 会将其解决为反连贯，执行打算中对应的关键字为ANTI。

SQL> select department_id,department_name from departments d where not exists (select 1 from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


SQL> select department_id,department_name from departments d where department_id not in (select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

SQL> select department_id,department_name from departments d where department_id<>all(select department_id from employees e where d.department_id = e.department_id and e.salary > 2500);

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3822487693

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN ANTI             |             |    17 |   391 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                |             |   105 |   735 |     4  (25)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL         | EMPLOYEES   |   105 |   735 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

欢送关注我的公众号，一起学习。

正文完
oracle
发表至： oracle
2021-02-09
 0
实用Oracle数据库常用命令整理
关于oracle:常用Oracle-SQL集锦
关于oracle:Oracle如何用内置脚本查堵塞
PLSQL-练习题5多条件限定-And字段1字段2