오라클 SQL조인, 중첩루프 조인(Nested Loop Join)
부분 범위처리에 유리한 조인 방식으로 두 개 이상의 테이블에서 먼저 접근하는 테이블(Driving/Outer Table)을 기준으로 순차적으로 다른 테이블(Driven/Inner Table)의 ROW(행, RECORD)를 조인하여 원하는 데이터를 추출하는 방식의 조인 입니다. 부분범위 처리시에 사용하며 확실히 범위를 줄일 조건들이 있을 때 사용하는 것이 좋습니다.
그림[6.6.1] 중첩루프조인
위 그림은 부서테이블(DEPT)과 사원테이블(EMP)을 조인하여 사원명, 부서명을 출력하는 쿼리문으로 중첩루프 조인의 동작을 나타낸 그림 입니다.
DEPT 테이블의 데이터는 4건으로 전체 테이블 데이터를 한 행씩 읽고, EMP 테이블의 deptno 컬럼은 외래키로서 DEPT 테이블의 deptno 컬럼을 참조하고 있으며 검색 효율을 위해 인덱스가 생성되어 있다고 가정을 하겠습니다. (인덱스란 검색 성능을 향상 시키기 위해 하나 이상의 컬럼의 값을 별도의 저장 영역에 값의 순서대로 저장해 놓은 오라클의 스키마 객체 입니다)
중첩루프조인이 동작하는 순서를 정리해 보겠습니다.
먼저 DEPT 테이블에 접근하는데 10번 “ACCOUNTING” 부서의 ROW를 읽은 후 조인 조건에 따라 EMP 테이블에서 deptno 컬럼 인덱스를 이용하여 10번 부서 사원들을 추출 합니다. 그런다음 결과 리스트에 담아두고 DEPT 테이블에서 20번 “RESEARCH” 부서의 ROW를 다시 읽은 후 EMP 테이블 20번 부서 사원들을 추출 합니다. 그런다음 결과 리스트에 담아두는 과정을 반복하여 40번 부서까지 처리 후 최종 결과를 반환합니다.
이때 DEPT 테이블을 먼저 읽는데, 이 테이블이 드라이빙/외부 테이블(Driving/Outer Table)이 되고 나중에 읽은 EMP 테이블이 드리븐/내부 테이블(Driven/Inner Table)이 됩니다.
중첩루프 조인은 추출되는 ROW가 많아질수록 성능이 현저히 떨어지며 성능을 높이기 위해서는 Driving Table의 크기가 작을수록 유리 합니다. Inner Table의 조인되는 컬럼인 EMP 테이블의 deptno 컬럼은 외래키로 DEPT 테이블의 deptno 컬럼을 참조하고 있으며 인덱스가 생성되어 있어야 검색 성능이 좋습니다.
Inner Table을 대체로 위 예문처럼 인덱스에 의해 접근하므로 추출되는 ROW가 많다면 수행속도가 저하 되며, Inner Table 조인 컬럼(EMP 테이블의 deptno)에 인덱스가 없다면 해시조인 또는 머지조인을 이용해야 합니다.
DEPT, EMP 테이블을 조인하여 사원번호, 사원명, 부서명을 출력하는 쿼리문 입니다.
오라클은 이러한 쿼리문을 중첩루프조인, 해시조인, 머지조인중 하나의 방법을 사용하여 SQL문을 처리 합니다. |
SELECT E.empno, E.ename, D.dname FROM dept D, emp E WHERE E.deptno = D.deptno |
<실행결과>
| EMPNO | ENAME | DNAME |
1 | 7782 | CLARK | ACCOUNTING |
2 | 7934 | MILLER | ACCOUNTING |
3 | 7788 | SCOTT | RESEARCH |
4 | 7876 | ADAMS | RESEARCH |
5 | 7369 | SMITH | RESEARCH |
6 | 7902 | FORD | RESEARCH |
7 | 7566 | JONES | RESEARCH |
8 | 7900 | JAMES | SALES |
9 | 7499 | ALLEN | SALES |
10 | 7698 | BLAKE | SALES |
11 | 7654 | MARTIN | SALES |
12 | 7844 | TURNER | SALES |
13 | 7521 | WARD | SALES |
위 SQL 구문을 SQL Developer에서 실행하면 필자의 경우 머지조인(MERGE JOIN) 형태로 조인을 실행하였습니다. 중첩루프조인을 유도하기 위해서는 뒤 쪽에서 배울 오라클 힌트 구문을 사용해야 합니다. (쿼리문에서 우측 마우스 클릭 후 설명 >> 계획설명(단축키 : F10)을 클릭하여 오라클이 SQL문장을 어떤 조인을 이용하여 실행하는지를 확인 할 수 있습니다.)
오라클에서 SQL문을 실행하는 경우 먼저 오라클 옵티마이저(Oracle Optimizer)가 SQL문장의 실행을 위해 테이블의 데이터를 어떤 형태로 접근하며, 어떤 조인순서로 조인을 하고, 어떤 조인을 사용할 것인지 등에 대해 대해 계획을 먼저 수립하는데 이를 “실행계획” 이라고 합니다. 이 실행계획을 만들고 그 실행 계획에 따라 실행을 해서 결과 데이터를 만듭니다.
이 실행계획은 테이블의 데이터 건수, 인덱스 현황, 테이블 및 인덱스에 대한 통계정보 생성등의 여부에 따라 달라 질 수 있습니다. 예를들어 서울에서 제주도 까지 간다고 했을 때 인천에서 배를 타고 갈수도 있고, 부산까지 고속버스 타고 배로 이동하여 갈수도 있고, 김포공항에서 비행기를 타고 갈 수 있습니다. 이 처럼 경로는 아주 다양 합니다. 대체로 오라클 옵티마이저가 최적의 실행경로를 만들지만 테이블이나 인덱스의 통계정보 부정확등 다양한 원인으로 인해 원하지 않는 실행경로를 만들 수 도 있기에 오라클 힌트 구문을 사용하여 SQL 작성자가 원하는 실행경로를 통해 데이터를 추출 하도록 지시하는 것 입니다.
오라클 힌트(Oracle Hint)는 오라클 옵티마이저에게 SQL문장을 실행할 때 어떤 경로로 데이터를 접근(Access Path)하고 어떤 조인방법, 조인순서를 사용하라는 것들을 강압적으로 지시하는 구문으로 남발하지 말고 꼭 필요한 경우에 절제하면서 사용해야 합니다. 실제로 SQL 튜닝은 이러한 Access Path, Join 방법 및 순서등을 적절히 조정해서 수행하게 됩니다.
대체로 오라클 서버는 최적의 데이터 접근 경로, 최적의 조인 방법, 조인 순서를 사용하여 SQL문을 실행하도록 구성되어 있습니다. |
ORDERED, USE_NL과 같은 오라클 힌트를 이용하여 쿼리문을 수정 후 다시 계획설명을 확인해 보겠습니다.
ORDERED 힌트는 FROM절에 나열한 테이블 순서(왼쪽에서 오른쪽으로)대로 조인을 하라는 뜻으로 DEPT 테이블이 드라이빙/외부 테이블이 되고 EMP 케이블이 드리븐/내부 테이블이 되어 조인을 하라는 힌트 구문 입니다.
USE_NL 힌트 구문은 중첩루프조인을 하라는 구문으로 인자로 드리븐/내부 테이블이 들어 갑니다. 물론 테이블 별칭을 사용했다면 별칭이 와야 합니다.
힌트 구문은 뒤에서 자세히 배워 보도록 합니다.
먼저 DETP 테이블의 내용을 조회해 보겠습니다. |
<실행결과>
| DEPTNO | DNAM | LOC |
1 | 10 | ACCOUNTING | NEW YORK |
2 | 20 | RESEARCH | DALLAS |
3 | 30 | SALES | CHICAGO |
4 | 40 | OPERATIONS | BOSTON |
오라클 힌트 구문을 사용하여 변경한 쿼리문장 입니다. 실행한 결과는 힌트를 사용하지 않은 SQL 문장과 동일하지만 SQL을 실행할 때 중첩루프조인을 사용하였습니다. 아래처럼 실행 >> 계획설명(단축키 : F10)을 통해 확인 할 수 있습니다. |
<실행순서>
1. DEPT 테이블을 FULL SCAN 하여 10, 20, 30, 40번 순서대로 ROW들을 읽는데, 처음 10번 부서 ROW를 읽습니다.
2. E.deptno = D.deptno를 수행 합니다. 현재 D.deptno는 10 이므로 상수 처리되어 E.deptno =10인 모양이 되어 아래 쿼리 형태로 실행 됩니다. SELECT E.empno, E.ename, D.dname FROM dept D, emp E WHERE E.deptno = 10 조건에 맞는 EMP 테이블의 empno, ename 컬럼의 값을 추출 합니다. 3. 20번 부서 데이터를 읽어 동일한 방식으로 처리 합니다. |
#SQL조인, #중첩루프조인, #NestedLoopJoin, #오라클중첩루프조인, #오라클, #오라클교육, #SQL, #SQL조인
