PT - Joins Methods and Examples

1. Nested Loop Join
2. hash table Join
3. Sort-merge Join

##########  Nested Loop Join #################

SQL> SELECT ENAME, DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO AND DEPT.DEPT_NO=10;

Execution Plan
----------------------------------------------------------
Plan hash value: 1706465873

------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                        |         |   113 |  3616 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID        | DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |   113 |  2147 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | DEPT_IX |   113 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPT_NO"=10)
   5 - access("EMP"."DEPT_NO"=10)

SQL>

##########  Hash table Join  #############

SQL> SELECT O.ORDER_ID, O.ORDER_DATE, C.CUST_LAST_NAME FROM ORDERS O,  CUSTOMERS C WHERE O.CUSTOMER_ID = C.CUSTOMER_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 3042513348

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|*  1 |  HASH JOIN         |           |  1376K|    45M|  5060   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CUSTOMERS | 46023 |   584K|   214   (1)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| ORDERS    |  1353K|    28M|  4839   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")

Note
-----
   - this is an adaptive plan

SQL>

###############  Sort-merge Join #############

SQL> SELECT ENAME , DNAME FROM DEPT, EMP WHERE DEPT.DEPT_NO = EMP.DEPT_NO;

Execution Plan
----------------------------------------------------------
Plan hash value: 2125045483

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |   879 | 28128 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     7 |    91 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | DEPT_PK |     7 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |   879 | 16701 |     6  (17)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |   879 | 16701 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPT_NO"="EMP"."DEPT_NO")
       filter("DEPT"."DEPT_NO"="EMP"."DEPT_NO")

SQL>