꿈꾸는 개발자, DBA 커뮤니티 구루비

05 Outer 조인

NL조이는 그 특성상 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택한다.
leading 힌트를 사용해서 순서를 바꿔 보려 해도 소용이 없다.
{CODE:SQL}

SQL> SELECT /*+ USE_NL( D E ) LEADING( E ) */ *
2 FROM SCOTT.DEPT D
3 , SCOTT.EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

...
15 개의 행이 선택되었습니다.

SQL> @XPLAN

-

Id	Operation	Name	Starts	E-Rows	A-Rows	A-Time	Buffers	록	0	SELECT STATEMENT	1	15	00:00:00.01	37
1	NESTED LOOPS OUTER		1	14	15	00:00:00.01	37
2	TABLE ACCESS FULL	DEPT	1	4	4	00:00:00.01	8
3	TABLE ACCESS FULL	EMP	4	4	14	00:00:00.01	29

-

Predicate Information (identified by operation id):

---

3 - filter("E"."DEPTNO"="D"."DEPTNO")

{CODE}

ERD 표기를 따르는 SQL 개발의 중요성 ( 이해가 안가신다고 해서 첨부함 ;; )

그림 2-27에서는 사원이 전형 없는 유령 부서가 등록될 수 있다.( null )
따라서 사원 유무와 상관업이 모든 부서가 출력되도록 하려면 사원 쪽 모든 조건절에 Outer 기호(+)를 반드시 붙여 줘야 한다.


SQL> DESC SCOTT.EMP;
 이름                                                                                                      널?      유형
 ----------------------------------------------------------------------------------------------------------------- -------- -----

 EMPNO                                                                                                     NOT NULL NUMBER(4)
 ENAME                                                                                                              VARCHAR2(10)
 JOB                                                                                                                VARCHAR2(9)
 MGR                                                                                                                NUMBER(4)
 HIREDATE                                                                                                           DATE
 SAL                                                                                                                NUMBER(7,2)
 COMM                                                                                                               NUMBER(7,2)
 DEPTNO                                                                                                             NUMBER(2)

SQL>  SELECT * FROM SCOTT.EMP ORDER BY EMPNO ASC;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 87/05/23       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10

 SQL>     INSERT INTO SCOTT.EMP VALUES( '7935', 'MILLER', 'CLERK', '7782', '82/01/23', '1300', NULL, NULL );

1 개의 행이 만들어졌습니다.

SQL> COMMIT;

커밋이 완료되었습니다.

SQL> SELECT * FROM SCOTT.EMP ORDER BY EMPNO ASC;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80/12/17        800                    20
      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
      7521 WARD       SALESMAN        7698 81/02/22       1250        500         30
      7566 JONES      MANAGER         7839 81/04/02       2975                    20
      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
      7782 CLARK      MANAGER         7839 81/06/09       2450                    10
      7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
      7839 KING       PRESIDENT            81/11/17       5000                    10
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
      7876 ADAMS      CLERK           7788 87/05/23       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 81/12/03        950                    30
      7902 FORD       ANALYST         7566 81/12/03       3000                    20
      7934 MILLER     CLERK           7782 82/01/23       1300                    10
      7935 MILLER     CLERK           7782 82/01/23       1300                         <== 이게 바로 유령부서임 ;;

15 개의 행이 선택되었습니다.

SQL>     SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE  FROM DBA_CONSTRAINTS
  2      WHERE OWNER = 'SCOTT';

TABLE_NAME                     CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
EMP                            FK_DEPTNO                      R
DEPT                           PK_DEPT                        P
EMP                            PK_EMP                         P

그림 2-28( page. 272 )에서는 사원이 없는 부서는 등록 될수 없다. ( 식별자 )
따라서 모든 부서가 출력되도록 하려겨고 굳이 Outer 조인할 필요가 없음에도 Outer 기호(+)를 붙인다면 성능이 나빠질 수 있다.

ERD 표기를 따르는 SQL 개발의 중요성 결론 : 그림 2-17과 2-18 모두 사원 쪽 부서번호가 필수컬럼이다.

소속 부서없이는 사원이 존재할 수 없다는 뜻이므로 테이블을 생성할 때 Not Null 제약을 두어야 한다.

(2) Outer 소트 머지 조인

소트된 중갑 집합을 이용한다는 점만 다를 뿐 처리루틴이 NL조인과 다르지않다고 했다.
따라서 Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 First 테이블로 선택된다.
{CODE:SQL}

SQL> SELECT /*+ USE_MERGE( D E ) LEADING( E ) */ *
2 FROM SCOTT.DEPT D
3 , SCOTT.EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

...
15 개의 행이 선택되었습니다.

SQL> @XPLAN

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

-

0	SELECT STATEMENT		1		15	00:00:00.01	11
1	MERGE JOIN OUTER		1	14	15	00:00:00.01	11
2	TABLE ACCESS BY INDEX ROWID	DEPT	1	4	4	00:00:00.01	4
3	INDEX FULL SCAN	PK_DEPT	1	4	4	00:00:00.01	2
4	SORT JOIN		4	14	14	00:00:00.01	7	2048	2048	2048 (0)
5	TABLE ACCESS FULL	EMP	1	14	14	00:00:00.01	7

-

Predicate Information (identified by operation id):

---

4 - access("E"."DEPTNO"="D"."DEPTNO")

{CODE}

(3) Outer 해시 조인

소트된 중갑 집합을 이용한다는 점만 다를 뿐 처리루틴이 NL조인과 다르지않다고 했다.
따라서 Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 First 테이블로 선택된다.

(3) Outer 해시 조인의 알고리즘 ( page. 275 그림 참조( 일반 Ourter 조인 ) )

1. Outer 집합인 dept 테이블을 해시 테이블로 빌드한다.
2. Inner 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다
3. 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시해 둔다.
4. Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태이다. 이제 조인에 실패 했던 레코드를 결과집합에 포함시켜야 하므로 해시 테이블을 스캔하면서
체크가 없는 dept 엔트리를 결과집합에 삽입한다.

{CODE:SQL}

SQL> SELECT /*+ USE_HASH( D E ) LEADING( E ) */ *
2 FROM TEST_DEPT D
3 , TEST_EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 80/12/17 800 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 81/04/02 2975 20
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 81/05/01 2850 30
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 81/06/09 2450 10
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 87/04/19 3000 20
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 81/11/17 5000 10
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 87/05/23 1100 20
30 SALES CHICAGO 7900 JAMES CLERK 7698 81/12/03 950 30
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 81/12/03 3000 20
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 82/01/23 1300 10
50 TEST TEST_LOC
40 OPERATIONS BOSTON

16 개의 행이 선택되었습니다.

SQL> @XPLAN

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

-

0	SELECT STATEMENT		1		16	00:00:00.01	11
1	HASH JOIN OUTER		1	14	16	00:00:00.01	11	1011K	1011K	743K (0)
2	TABLE ACCESS FULL	TEST_DEPT	1	5	5	00:00:00.01	7
3	TABLE ACCESS FULL	TEST_EMP	1	14	14	00:00:00.01	4

-

Predicate Information (identified by operation id):

---

1 - access("E"."DEPTNO"="D"."DEPTNO")

-- 역순
SQL> SELECT /*+ USE_HASH( D E ) LEADING( E ) */ *
2 FROM (SELECT IN_E.*, ROWNUM FROM TEST_DEPT IN_E ORDER BY DEPTNO DESC ) D
3 , TEST_EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

DEPTNO DNAME LOC ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
20 RESEARCH DALLAS 4 7369 SMITH CLERK 7902 80/12/17 800 20
30 SALES CHICAGO 3 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
30 SALES CHICAGO 3 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
20 RESEARCH DALLAS 4 7566 JONES MANAGER 7839 81/04/02 2975 20
30 SALES CHICAGO 3 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
30 SALES CHICAGO 3 7698 BLAKE MANAGER 7839 81/05/01 2850 30
10 ACCOUNTING NEW YORK 5 7782 CLARK MANAGER 7839 81/06/09 2450 10
20 RESEARCH DALLAS 4 7788 SCOTT ANALYST 7566 87/04/19 3000 20
10 ACCOUNTING NEW YORK 5 7839 KING PRESIDENT 81/11/17 5000 10
30 SALES CHICAGO 3 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
20 RESEARCH DALLAS 4 7876 ADAMS CLERK 7788 87/05/23 1100 20
30 SALES CHICAGO 3 7900 JAMES CLERK 7698 81/12/03 950 30
20 RESEARCH DALLAS 4 7902 FORD ANALYST 7566 81/12/03 3000 20
10 ACCOUNTING NEW YORK 5 7934 MILLER CLERK 7782 82/01/23 1300 10
50 TEST TEST_LOC 1
40 OPERATIONS BOSTON 2

16 개의 행이 선택되었습니다.

SQL> @xplan

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

0	SELECT STATEMENT		1		16	00:00:00.01	7
1	HASH JOIN OUTER		1	14	16	00:00:00.01	7	972K	972K	751K (0)
2	VIEW		1	5	5	00:00:00.01	3
3	COUNT		1		5	00:00:00.01	3
4	TABLE ACCESS BY INDEX ROWID	TEST_DEPT	1	5	5	00:00:00.01	3
5	INDEX FULL SCAN DESCENDING	TEST_DEPT_PK	1	5	5	00:00:00.01	1
6	TABLE ACCESS FULL	TEST_EMP	1	14	14	00:00:00.01	4

Predicate Information (identified by operation id):

---

1 - access("E"."DEPTNO"="D"."DEPTNO")

{CODE}

Right Outer 해시 조인

Outer 조인할때 조인 순서가 고정되므로 자주 성능문제를 일으키곤 했다.
ex : 주문테이블을 기준으로 고객테이블과 Outer 조인하는 경우에 대용량 주문 테이블을 해시 테이블로 빌드해야 하는 문제가 발생
Hash Area가 부족해 디스크 쓰기와 읽기가 밸생할 뿐만 아니라 주문 건수가 많은 고객일수록 해시 버킷 당 엔트리 개수가 많아져 해시 테이블을 탐색하는 효율이 크게 저하된다.
Right Outer 해시 조인은 결구 Outer NL 조인과 같은 알고리즘을 사용한다.

{CODE:SQL}

Right Outer 해시 조인 알고리즘 ( page. 275 그림 참조( 일반 Ourter 조인 ) )

1. Inner 집합인 dept 테이블을 해시 테이블로 빌드한다
2. Outer 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다.
3. Outer 조인이므로 조인 성공 여부에 상관없이 결과집합에 삽입한다.

SQL> SELECT /*+ USE_HASH( D E ) SWAP_JOIN_INPUTS( E ) */ *
2 FROM TEST_DEPT D
3 , TEST_EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 82/01/23 1300 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 81/11/17 5000 10
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 81/06/09 2450 10
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 81/12/03 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 87/05/23 1100 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 87/04/19 3000 20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 81/04/02 2975 20
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 80/12/17 800 20
30 SALES CHICAGO 7900 JAMES CLERK 7698 81/12/03 950 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 81/05/01 2850 30

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
40 OPERATIONS BOSTON
50 TEST TEST_LOC

16 개의 행이 선택되었습니다.

SQL> @XPLAN

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

0	SELECT STATEMENT		1		16	00:00:00.01	11
1	HASH JOIN RIGHT OUTER		1	14	16	00:00:00.01	11	865K	865K	646K (0)
2	TABLE ACCESS FULL	TEST_EMP	1	14	14	00:00:00.01	3
3	TABLE ACCESS FULL	TEST_DEPT	1	5	5	00:00:00.01	8

Predicate Information (identified by operation id):

---

1 - access("E"."DEPTNO"="D"."DEPTNO")

-- 역순
SQL> SELECT /*+ USE_HASH( D E ) SWAP_JOIN_INPUTS( E ) */ *
2 FROM (SELECT IN_E.*, ROWNUM FROM TEST_DEPT IN_E ORDER BY DEPTNO DESC ) D
3 ,TEST_EMP E
4 WHERE E.DEPTNO(+) = D.DEPTNO;

DEPTNO DNAME LOC ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
50 TEST TEST_LOC 1
40 OPERATIONS BOSTON 2
30 SALES CHICAGO 3 7900 JAMES CLERK 7698 81/12/03 950 30
30 SALES CHICAGO 3 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
30 SALES CHICAGO 3 7698 BLAKE MANAGER 7839 81/05/01 2850 30
30 SALES CHICAGO 3 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
30 SALES CHICAGO 3 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
30 SALES CHICAGO 3 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
20 RESEARCH DALLAS 4 7902 FORD ANALYST 7566 81/12/03 3000 20
20 RESEARCH DALLAS 4 7876 ADAMS CLERK 7788 87/05/23 1100 20
20 RESEARCH DALLAS 4 7788 SCOTT ANALYST 7566 87/04/19 3000 20

DEPTNO DNAME LOC ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
20 RESEARCH DALLAS 4 7566 JONES MANAGER 7839 81/04/02 2975 20
20 RESEARCH DALLAS 4 7369 SMITH CLERK 7902 80/12/17 800 20
10 ACCOUNTING NEW YORK 5 7934 MILLER CLERK 7782 82/01/23 1300 10
10 ACCOUNTING NEW YORK 5 7839 KING PRESIDENT 81/11/17 5000 10
10 ACCOUNTING NEW YORK 5 7782 CLARK MANAGER 7839 81/06/09 2450 10

16 개의 행이 선택되었습니다.

SQL> @XPLAN

Plan hash value: 663895112

-

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

Reads

OMem

1Mem

Used-Mem

-

0	SELECT STATEMENT		1		16	00:00:00.01	7	1
1	HASH JOIN RIGHT OUTER		1	14	16	00:00:00.01	7	1	865K	865K	700K (0)
2	TABLE ACCESS FULL	TEST_EMP	1	14	14	00:00:00.01	3	0
3	VIEW		1	5	5	00:00:00.01	4	1
4	COUNT		1		5	00:00:00.01	4	1
5	TABLE ACCESS BY INDEX ROWID	TEST_DEPT	1	5	5	00:00:00.01	4	1
6	INDEX FULL SCAN DESCENDING	TEST_DEPT_PK	1	5	5	00:00:00.01	2	1

-

Predicate Information (identified by operation id):

---

1 - access("E"."DEPTNO"="D"."DEPTNO")

{CODE}

Right Outer 해시 조인 탄생배경

고객 없는 주문 또는 상품 없는 주문은 존재 하지 않도록 설계되었다.
오라클은 이런 사실을 감안해 Outer 테이블을 해시 테이블로 빌드하는 알고리즘을 애초에 선택하였다.
Inner 조인하고 나서 해시 테이블을 전체적으로 한 번 더 스캔하는 비효율을 감수하면서 까지..( 작은 쪽 집합을 해시 테이블로 빌드하는 게 유리하므로 )
고객 없는 주문 또는 상품 없는 주문은 존재 하지 않도록 설계되었지만 FK를 설정하지 않은 채 운영하다보니 잘못된 레코드가 생성됨.
어쩔수 없이 대용량 테이블인 주문 테이블을 빌드해야함.
오라클은 이런 성능 이슈를 해결하기 위하여 10부터 Inner쪽 집합을 해시 테이블로 빌드할 수 있는 알고리즘 추가함.

9i 이전 버전에서 Outer 해시 조인 튜닝

{CODE:SQL}
SQL> SELECT /*+ ORDERED INDEX_FFS( E ) FULL( D ) FULL( E2 ) USE_HASH( E D E2) PARALLEL_INDEX( E ) PARALLEL( D ) PARALLEL( E2 ) / D., E2.*
2 FROM TEST_EMP E, TEST_DEPT D, TEST_EMP E2
3 WHERE E.DEPTNO = D.DEPTNO(+)
4 AND E.EMPNO = E2.EMPNO ;

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 87/05/23 1100 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 87/04/19 3000 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 81/12/03 3000 20
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 81/11/17 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 82/01/23 1300 10
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 80/12/17 800 20
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 81/06/09 2450 10
30 SALES CHICAGO 7900 JAMES CLERK 7698 81/12/03 950 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 81/02/22 1250 500 30

DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

-

--

-

--

--
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 81/05/01 2850 30
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 81/04/02 2975 20
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30

14 개의 행이 선택되었습니다.

SQL> @XPLAN

--

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

--

0	SELECT STATEMENT		1		14	00:00:00.03	9
1	PX COORDINATOR		1		14	00:00:00.03	9
2	PX SEND QC (RANDOM)	:TQ10004	0	14	0	00:00:00.01	0
3	HASH JOIN BUFFERED		0	14	0	00:00:00.01	0	1155K	1155K	813K (0)
4	PX RECEIVE		0	14	0	00:00:00.01	0
5	PX SEND HASH	:TQ10002	0	14	0	00:00:00.01	0
6	HASH JOIN OUTER BUFFERED		0	14	0	00:00:00.01	0	1524K	1524K	566K (0)
7	PX RECEIVE		0	14	0	00:00:00.01	0
8	PX SEND HASH	:TQ10000	0	14	0	00:00:00.01	0
9	PX BLOCK ITERATOR		0	14	0	00:00:00.01	0
10	INDEX FAST FULL SCAN	TEST_EMP_PK	0	14	0	00:00:00.01	0
11	PX RECEIVE		0	5	0	00:00:00.01	0
12	PX SEND HASH	:TQ10001	0	5	0	00:00:00.01	0
13	PX BLOCK ITERATOR		0	5	0	00:00:00.01	0
14	TABLE ACCESS FULL	TEST_DEPT	0	5	0	00:00:00.01	0
15	PX RECEIVE		0	14	0	00:00:00.01	0
16	PX SEND HASH	:TQ10003	0	14	0	00:00:00.01	0
17	PX BLOCK ITERATOR		0	14	0	00:00:00.01	0
18	TABLE ACCESS FULL	TEST_EMP	0	14	0	00:00:00.01	0

--

Predicate Information (identified by operation id):

---

3 - access("E"."EMPNO"="E2"."EMPNO")
6 - access("E"."DEPTNO"="D"."DEPTNO")
10 - access(:Z>=:Z AND :Z<=:Z)
14 - access(:Z>=:Z AND :Z<=:Z)
18 - access(:Z>=:Z AND :Z<=:Z)

{CODE}

Outer 집합인 E 테이블의 PK만 빠르게 읽어 인덱스 블록만 읽더라도 In-Memory 해시 조인은 불가능하겠지만 Build Input 크기를 줄임으로써 디스크 쓰기 및 읽기 작업을 최소화하려는 아이디어
디스크 쓰기와 읽기 작업을 줄여주는 효과는 있지만 해시 버킷 당 엔트리 개수가 많아서 생기는 문제는 필할수없다.
만약 주문 테이블이 주문일시 Range 파티션 테이블이라면, 일정한 주문일시 구간 내에서의 고객별 주문 건수는 많지 않을 것이기 때문에해시 버킷 당 엔트리 개수를 최소화 할 수 있다
그러면 고객 테이블을 반복적으로 읽는 비효율에도 불구하고 더 빠르게 수행될 것이다.
h3 Full Outer 조인
{CODE:SQL}

SQL> EXEC DBMS_RANDOM.SEED( 150 ) ;

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> CREATE TABLE 입금 AS
2 SELECT ROWNUM 일련번호
3 , ROUND(DBMS_RANDOM.VALUE(1, 20)) 고객ID
4 , ROUND(DBMS_RANDOM.VALUE(1000, 1000000), -2) 입금액
5 FROM dual CONNECT BY LEVEL <= 10
6 ;

테이블이 생성되었습니다.

SQL> CREATE TABLE 출금 AS
2 SELECT ROWNUM 일련번호
3 , ROUND(DBMS_RANDOM.VALUE(1, 20)) 고객ID
4 , ROUND(DBMS_RANDOM.VALUE(1000, 1000000), -2) 출금액
5 FROM dual CONNECT BY LEVEL <= 10;

테이블이 생성되었습니다.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '입금');

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, '출금');

PL/SQL 처리가 정상적으로 완료되었습니다.

{CODE}

Left Outer 조인 + Union all + Anti 조인( Not Exists 필터 ) 이용

{CODE:SQL}

SQL> SELECT A.고객ID, A.입금액, B.출금액
2 FROM ( SELECT 고객ID, SUM( 입금액 ) 입금액 FROM 입금 GROUP BY 고객ID ) A
3 , ( SELECT 고객ID, SUM( 출금액 ) 출금액 FROM 출금 GROUP BY 고객ID ) B
4 WHERE B.고객ID(+) = A.고객ID
5 UNION ALL
6 SELECT 고객ID, NULL, 출금액
7 FROM (SELECT 고객ID, SUM( 출금액 ) 출금액 FROM 출금 GROUP BY 고객ID) A
8 WHERE NOT EXISTS (SELECT 'X' FROM 입금 WHERE 고객ID = A.고객ID);

고객ID 입금액 출금액

--

--
13 59400 83100
4 1211700 76200
3 259400 701000
18 336900 1445600
19 398300 781200
8 957000
1 224100
2 231900
6 707000
11 70300
17 296700

고객ID 입금액 출금액

--

--
9 382600

12 개의 행이 선택되었습니다.

SQL> @XPLAN

---

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

---

0	SELECT STATEMENT		1		12	00:00:00.01	12
1	UNION-ALL		1		12	00:00:00.01	12
2	HASH JOIN OUTER		1	9	9	00:00:00.01	6	1269K	1269K	1144K (0)
3	VIEW		1	9	9	00:00:00.01	3
4	HASH GROUP BY		1	9	9	00:00:00.01	3	948K	948K	2206K (0)
5	TABLE ACCESS FULL	입금	1	10	10	00:00:00.01	3
6	VIEW		1	8	8	00:00:00.01	3
7	HASH GROUP BY		1	8	8	00:00:00.01	3	948K	948K	1705K (0)
8	TABLE ACCESS FULL	출금	1	10	10	00:00:00.01	3
9	HASH GROUP BY		1	8	3	00:00:00.01	6	948K	948K	709K (0)
10	HASH JOIN ANTI		1	10	3	00:00:00.01	6	1269K	1269K	912K (0)
11	TABLE ACCESS FULL	출금	1	10	10	00:00:00.01	3
12	TABLE ACCESS FULL	입금	1	10	10	00:00:00.01	3

---

Predicate Information (identified by operation id):

---

2 - access("B"."고객ID"="A"."고객ID")
10 - access("고객ID"="고객ID")

{CODE}

ANSI FULL Outer 조인( Native Hash Full Outer 조인 )

9i
Left Outer 조인 + Union all + Anti 조인( Not Exists 필터 ) 이용 같은 실행계획 수립
10.2.0.4 : opt_param('_optimizer_native_full_outer_join', 'force')
{CODE:SQL}
SQL> SELECT nvl(A.고객ID, B.고객ID), A.입금액, B.출금액
2 FROM ( SELECT 고객ID, SUM( 입금액 ) 입금액 FROM 입금 GROUP BY 고객ID ) A
3 FULL OUTER JOIN
4 ( SELECT 고객ID, SUM( 출금액 ) 출금액 FROM 출금 GROUP BY 고객ID ) B
5 ON A.고객ID = B.고객ID;

NVL(A.고객ID,B.고객ID) 입금액 출금액

--

--
1 224100
6 707000
13 59400 83100
2 231900
4 1211700 76200
8 957000
3 259400 701000
18 336900 1445600
19 398300 781200
11 70300
17 296700
9 382600

12 개의 행이 선택되었습니다.

SQL> @XPLAN

---

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

---

0	SELECT STATEMENT		1		12	00:00:00.01	6
1	VIEW	VW_FOJ_0	1	9	12	00:00:00.01	6
2	HASH JOIN FULL OUTER		1	9	12	00:00:00.01	6	1269K	1269K	885K (0)
3	VIEW		1	8	8	00:00:00.01	3
4	HASH GROUP BY		1	8	8	00:00:00.01	3	948K	948K	1706K (0)
5	TABLE ACCESS FULL	출금	1	10	10	00:00:00.01	3
6	VIEW		1	9	9	00:00:00.01	3
7	HASH GROUP BY		1	9	9	00:00:00.01	3	948K	948K	2210K (0)
8	TABLE ACCESS FULL	입금	1	10	10	00:00:00.01	3

---

Predicate Information (identified by operation id):

---

2 - access("A"."고객ID"="B"."고객ID")
{CODE}

1. 출금테이블을 해시 테이블로 빌드 한다.
2. 입금테이블로 해시 테이블을 탐색하면서 조인한다.
3. 조인 성공여부에 상관없이 결과 집합에 삽입하고, 조인에 성공한 출금 래코드에 체크 표시를 해 둔다.
4. Probe 단계가 끝나면 Right Outer 해시 조인한 것과 동일한 결과집합이 만들어진다. 이제 해시 테이블을 스캔하면서
체크 표시가 없는 출금레코드를 결과집합에 삽입함으로써 Full Outer 조인을 완성한다. 입금액이 마지막에 출력된 이유가 바로 이것이다.

Union All을 이용한 Full Outer 조인

{CODE:SQL}

SQL> SELECT 고객ID, SUM( 입금액 ) 입금액, SUM( 출금액 ) 출금액
2 FROM (
3 SELECT 고객ID, 입금액, TO_NUMBER(NULL) 출금액 FROM 입금
4 UNION ALL
5 SELECT 고객ID, TO_NUMBER(NULL) , 출금액 FROM 출금
6 )
7 GROUP BY 고객ID;

고객ID 입금액 출금액

--

--
1 224100
6 707000
13 59400 83100
11 70300
2 231900
4 1211700 76200
8 957000
17 296700
3 259400 701000
18 336900 1445600
19 398300 781200
9 382600

12 개의 행이 선택되었습니다.

SQL> @XPLAN

--

Id

Operation

Name

Starts

E-Rows

A-Rows

A-Time

Buffers

OMem

1Mem

Used-Mem

--

0	SELECT STATEMENT		1		12	00:00:00.01	6
1	HASH GROUP BY		1	9	12	00:00:00.01	6	843K	843K	1213K (0)
2	VIEW		1	20	20	00:00:00.01	6
3	UNION-ALL		1		20	00:00:00.01	6
4	TABLE ACCESS FULL	입금	1	10	10	00:00:00.01	3
5	TABLE ACCESS FULL	출금	1	10	10	00:00:00.01	3

--

19 개의 행이 선택되었습니다.

{CODE}

5. Outer 조인 (by ljh1148) [2012.10.05]

05 Outer 조인

ERD 표기를 따르는 SQL 개발의 중요성 ( 이해가 안가신다고 해서 첨부함 ;; )

ERD 표기를 따르는 SQL 개발의 중요성 결론 : 그림 2-17과 2-18 모두 사원 쪽 부서번호가 필수컬럼이다.

(2) Outer 소트 머지 조인

(3) Outer 해시 조인

(3) Outer 해시 조인의 알고리즘 ( page. 275 그림 참조( 일반 Ourter 조인 ) )

Right Outer 해시 조인

Right Outer 해시 조인 알고리즘 ( page. 275 그림 참조( 일반 Ourter 조인 ) )

Right Outer 해시 조인 탄생배경

9i 이전 버전에서 Outer 해시 조인 튜닝

Left Outer 조인 + Union all + Anti 조인( Not Exists 필터 ) 이용

ANSI FULL Outer 조인( Native Hash Full Outer 조인 )

Union All을 이용한 Full Outer 조인