- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26740578&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
05 Outer 조인
- NL조이는 그 특성상 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer 기호
가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택한다. - leading 힌트를 사용해서 순서를 바꿔 보려 해도 소용이 없다.
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")
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 테이블로 선택된다.
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")
(3) Outer 해시 조인
- 소트된 중갑 집합을 이용한다는 점만 다를 뿐 처리루틴이 NL조인과 다르지않다고 했다.
- 따라서 Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호가 붙지 않은 테이블이 항상 First 테이블로 선택된다.
(3) Outer 해시 조인의 알고리즘 ( page. 275 그림 참조( 일반 Ourter 조인 ) )
- 1. Outer 집합인 dept 테이블을 해시 테이블로 빌드한다.
- 2. Inner 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다
- 3. 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시해 둔다.
- 4. Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태이다. 이제 조인에 실패 했던 레코드를 결과집합에 포함시켜야 하므로 해시 테이블을 스캔하면서
체크가 없는 dept 엔트리를 결과집합에 삽입한다.
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")
Right Outer 해시 조인
- Outer 조인할때 조인 순서가 고정되므로 자주 성능문제를 일으키곤 했다.
- ex : 주문테이블을 기준으로 고객테이블과 Outer 조인하는 경우에 대용량 주문 테이블을 해시 테이블로 빌드해야 하는 문제가 발생
- Hash Area가 부족해 디스크 쓰기와 읽기가 밸생할 뿐만 아니라 주문 건수가 많은 고객일수록 해시 버킷 당 엔트리 개수가 많아져 해시 테이블을 탐색하는 효율이 크게 저하된다.
- Right Outer 해시 조인은 결구 Outer NL 조인과 같은 알고리즘을 사용한다.
h3. 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")
Right Outer 해시 조인 탄생배경
- 고객 없는 주문 또는 상품 없는 주문은 존재 하지 않도록 설계되었다.
- 오라클은 이런 사실을 감안해 Outer 테이블을 해시 테이블로 빌드하는 알고리즘을 애초에 선택하였다.
- Inner 조인하고 나서 해시 테이블을 전체적으로 한 번 더 스캔하는 비효율을 감수하면서 까지..( 작은 쪽 집합을 해시 테이블로 빌드하는 게 유리하므로 )
- 고객 없는 주문 또는 상품 없는 주문은 존재 하지 않도록 설계되었지만 FK를 설정하지 않은 채 운영하다보니 잘못된 레코드가 생성됨.
- 어쩔수 없이 대용량 테이블인 주문 테이블을 빌드해야함.
- 오라클은 이런 성능 이슈를 해결하기 위하여 10부터 Inner쪽 집합을 해시 테이블로 빌드할 수 있는 알고리즘 추가함.
9i 이전 버전에서 Outer 해시 조인 튜닝
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)
- Outer 집합인 E 테이블의 PK만 빠르게 읽어 인덱스 블록만 읽더라도 In-Memory 해시 조인은 불가능하겠지만 Build Input 크기를 줄임으로써 디스크 쓰기 및 읽기 작업을 최소화하려는 아이디어
- 디스크 쓰기와 읽기 작업을 줄여주는 효과는 있지만 해시 버킷 당 엔트리 개수가 많아서 생기는 문제는 필할수없다.
- 만약 주문 테이블이 주문일시 Range 파티션 테이블이라면, 일정한 주문일시 구간 내에서의 고객별 주문 건수는 많지 않을 것이기 때문에해시 버킷 당 엔트리 개수를 최소화 할 수 있다
- 그러면 고객 테이블을 반복적으로 읽는 비효율에도 불구하고 더 빠르게 수행될 것이다.
h3 Full Outer 조인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 처리가 정상적으로 완료되었습니다.
Left Outer 조인 + Union all + Anti 조인( Not Exists 필터 ) 이용
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")
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')
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")
- 1. 출금테이블을 해시 테이블로 빌드 한다.
- 2. 입금테이블로 해시 테이블을 탐색하면서 조인한다.
- 3. 조인 성공여부에 상관없이 결과 집합에 삽입하고, 조인에 성공한 출금 래코드에 체크 표시를 해 둔다.
- 4. Probe 단계가 끝나면 Right Outer 해시 조인한 것과 동일한 결과집합이 만들어진다. 이제 해시 테이블을 스캔하면서
체크 표시가 없는 출금레코드를 결과집합에 삽입함으로써 Full Outer 조인을 완성한다. 입금액이 마지막에 출력된 이유가 바로 이것이다.
Union All을 이용한 Full Outer 조인
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 개의 행이 선택되었습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=26740578&
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.