Table connection method

Time:2021-7-5

There are four ways to join Oracle tables

Sort merge join

Sort merge join is to sort two connected tables with join columns, merge the sorted result set, and then get matching records. If there is an index on the join column to avoid sorting, the optimizer may choose to sort and merge the join. It can be used for =, >, > =, <, < = connection condition, but not for < >, like connection condition. The corresponding execution plan isSORT JOINandMERGE 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 |
--------------------------------------------------------------------------------------------

--If there is no index, the hash connection is selected:
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 |
---------------------------------------------------------------------------

--If the unequal join condition is used, the nested loop join is selected:
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

Nested loop connection is that Oracle will connect two tables to determine the driven table and the driven table according to the size of the result set. The small result set is used as the driving table, and the large result set is used as the driven table. For each row of the driven table, the join condition should be used to match all rows of the driven table. It is suitable for table join with small result set of driving table and efficient index of driven table on join column. Can be used for all connection conditions. The corresponding execution plan isNESTED 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 |
--------------------------------------------------------------------------------------------

--Here, after conditional filtering is added to the departments table, the result set has only one row, so it is selected as the drive table.


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

There are two nested loops because Oracle 11g introduces vector I / O to batch process multiple physical I / O requests to improve the efficiency of nested loop connection.

If there is an index in the restricted field of the driving table and in the join condition field of the driven table, the efficiency of nested loop join will be very high.

Hash join

If the result set of two tables is very large, the cost of sorting is high; If nested loops are used, the number of loops is large, and the result set of the driven table needs to be accessed many times; In order to improve the efficiency of table join in this case, the optimizer provides a new table join method, namely hash join. Hash join is a table join method that uses hash operation to get the result. It is only applicable to the equivalent connection condition.

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

If two tables join without join condition, Cartesian product will be produced. Cartesian product should be avoided as far as possible in practical work. There are keywords in the execution plan corresponding to Cartesian productMERGE 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 |
-----------------------------------------------------------------------------

other

External connection

The previous examples of connection methods are all internal connections. We know that Oracle also has external connections, including left external connection, right external connection and all external connection. The keywords corresponding to the left outer connection and the right outer connection in the execution plan areOUTER, the keyword corresponding to all external connections isFULL OUTER

--Internal connection:
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 |
--------------------------------------------------------------------------------------------

--Left external connection:
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 |
----------------------------------------------------------------------------------

--Right outer connection:
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 |
--------------------------------------------------------------------------------------------

--All external connections:
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 |
-------------------------------------------------------------------------------------

Semi connection

If the where condition has exists, in or = any operator + subquery, Oracle will treat it as a semi join, and the corresponding keyword in the execution plan isSEMI

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

Anti connection

If the where condition has not exists, not in or < > all operator + subquery, Oracle will treat it as anti join, and the corresponding keyword in the execution plan isANTI

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

Welcome to my official account and study together.

Table connection method