p.72 이너-조인과 아우터-조인의 비교

아우터는 조인조건절에 (+) 를 명시한다

SELECT  T1.CUS_ID
		,T1.CUS_NM
		,T2.CUS_ID
		,T2.ITM_ID
		,T2.EVL_LST_NO
FROM    M_CUS T1
		,T_ITM_EVL T2
WHERE   T1.CUS_ID = 'CUS_0002'
AND     T1.CUS_ID = T2.CUS_ID;
  CUS_ID      CUS_NM    CUS_ID    ITM_ID    EVL_LST_NO
------------------------------------------------------
(결과집합 없음)

SELECT  T1.CUS_ID
		,T1.CUS_NM
		,T2.CUS_ID
		,T2.ITM_ID
		,T2.EVL_LST_NO
FROM    M_CUS T1
		,T_ITM_EVL T2
WHERE   T1.CUS_ID = 'CUS_0002'
AND     T1.CUS_ID = T2.CUS_ID(+);

  CUS_ID      CUS_NM  |  CUS_ID    ITM_ID    EVL_LST_NO
----------------------| --------------------------------
CUS_0002   NAME_0002  |   NULL      NULL          NULL

p.73 아우터조인 결과집합의 형태

-- CUS_0011은 평가데이터가 있음
-- CUS_0002는 평가데이터가 없음
SELECT  T1.CUS_ID ,T1.CUS_NM
			,T2.CUS_ID ,T2.ITM_ID ,T2.EVL_LST_NO
FROM    M_CUS T1
			,T_ITM_EVL T2
WHERE   T1.CUS_ID IN ('CUS_0002','CUS_0011')
AND     T1.CUS_ID = T2.CUS_ID(+)
ORDER BY T1.CUS_ID;

| CUS_ID   | CUS_NM    | CUS_ID   | ITM_ID | EVL_LST_NO |
| -------- | --------- | -------- | ------ | ---------- |
| CUS_0002 | NAME_0002 |      null|    null|        null|
| CUS_0011 | NAME_0011 | CUS_0011 | ITM001 | 8          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM011 | 9          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM021 | 8          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM031 | 7          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM041 | 8          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM051 | 7          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM061 | 8          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM071 | 8          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM081 | 7          |
| CUS_0011 | NAME_0011 | CUS_0011 | ITM091 | 2          |

Outer조인순서에 따른 결과집합 차이

outer조인 (기본쿼리)

Outer : M_CUS
Inner(+) : T_ITM_EVL

SELECT  /*+  */ T1.CUS_ID ,T1.CUS_NM
			,T2.CUS_ID ,T2.ITM_ID ,T2.EVL_LST_NO
FROM    M_CUS T1
		,T_ITM_EVL T2
WHERE   T1.CUS_ID IN ('CUS_0002','CUS_0011')
AND     T1.CUS_ID = T2.CUS_ID(+)
ORDER BY T1.CUS_ID;

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     9 |   342 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER           |           |     9 |   342 |     6   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS     |     2 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_M_CUS  |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | T_ITM_EVL |     4 |    76 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

outer조인시 조인순서 강제로 역행을 시도

아래 힌트대로면 다음과 같은 결과를 기대했다
- Outer : T_ITM_EVL
- Inner(+) : M_CUS
실패 (즉, 아우터조인의 조인순서는 항상 고정적)
- Outer : M_CUS
- Inner(+) : T_ITM_EVL

SELECT  /*+ leading(T2 T1) use_nl(T1) */ T1.CUS_ID ,T1.CUS_NM
			,T2.CUS_ID ,T2.ITM_ID ,T2.EVL_LST_NO
FROM    M_CUS T1
		,T_ITM_EVL T2
WHERE   T1.CUS_ID IN ('CUS_0002','CUS_0011')
AND     T1.CUS_ID = T2.CUS_ID(+)
ORDER BY T1.CUS_ID;

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     9 |   342 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER           |           |     9 |   342 |     6   (0)| 00:00:01 |
|   2 |   INLIST ITERATOR             |           |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| M_CUS     |     2 |    38 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | PK_M_CUS  |     2 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | T_ITM_EVL |     4 |    76 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Hash조인 순서에 따른 결과집합

SELECT /*+ use_hash(d e) */ 
			e.EMPNO, e.ENAME, d.DNAME 
FROM emp e, dept d
WHERE d.DEPTNO = e.DEPTNO(+); 

-- emp가 프로브 dept가 해시테이블
-- emp순서대로 정렬됨
| EMPNO | ENAME  | DNAME      |
| ----- | ------ | ---------- |
| 7369  | SMITH  | RESEARCH   |
| 7499  | ALLEN  | SALES      |
| 7521  | WARD   | SALES      |
| 7566  | JONES  | RESEARCH   |
| 7654  | MARTIN | SALES      |
| 7698  | BLAKE  | SALES      |
| 7782  | CLARK  | ACCOUNTING |
| 7788  | SCOTT  | RESEARCH   |
| 7839  | KING   | ACCOUNTING |
| 7844  | TURNER | SALES      |
| 7876  | ADAMS  | RESEARCH   |
| 7900  | JAMES  | SALES      |
| 7902  | FORD   | RESEARCH   |
| 7934  | MILLER | ACCOUNTING |
| 7935  | WONJIN | MARKETING  |
|       |        | OPERATIONS |

SELECT /*+ use_hash(e d) swap_join_inputs(e) */ 
			e.EMPNO, e.ENAME, d.DNAME 
FROM emp e, dept d
WHERE d.DEPTNO = e.DEPTNO(+); 

-- dept가 프로브 emp가 해시테이블
-- dept순서대로 정렬됨
| EMPNO | ENAME  | DNAME      |
| ----- | ------ | ---------- |
| 7782  | CLARK  | ACCOUNTING |
| 7839  | KING   | ACCOUNTING |
| 7934  | MILLER | ACCOUNTING |
| 7369  | SMITH  | RESEARCH   |
| 7566  | JONES  | RESEARCH   |
| 7788  | SCOTT  | RESEARCH   |
| 7876  | ADAMS  | RESEARCH   |
| 7902  | FORD   | RESEARCH   |
| 7499  | ALLEN  | SALES      |
| 7521  | WARD   | SALES      |
| 7654  | MARTIN | SALES      |
| 7698  | BLAKE  | SALES      |
| 7844  | TURNER | SALES      |
| 7900  | JAMES  | SALES      |
|       |        | OPERATIONS |
| 7935  | WONJIN | MARKETING  |