오라클 SQL조인, 안티 조인(Anti Join)

 

오라클 SQL조인, 안티 조인(Anti Join)

조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 조인방식 입니다.
 

서브 쿼리의 테이블에는 없는 메인 쿼리의 테이블의 데이터만 추출하는 조인 방법으로 NOT IN, NOT EXISTS 사용시 발생하는 조인연산이며 오라클에서 중첩루프조인 안티, 머지조인 안티, 해시 조인 안티로 실행 됩니다.
 

NOT IN을 이용한 안티 조인 예문 입니다. EMP 테이블에서 사번, 성명, 부서코드를 추출하는데 부서코드를 가지고 있지않은 사원들만 추출하는 쿼리 문 입니다. “KING” 사원은 최고관리자로 부서코드(deptno) 값을 가지고 있지않지만  NULL값이므로  결과 대상에서 제외 되었습니다.(NULL과 연산하는 결과는 NULL이 됨)

-- NOT IN SELECT empno, ename, deptno FROM emp WHERE deptno NOT IN (   SELECT deptno   FROM dept )

 

<실행결과>

선택된 행 없음

<실행계획(F10)>

오라클19C에서 별다른 힌트구문없이 그냥 실행 했을 때 해시조인 안티로 실행계획을 수립함을 확인할 수 있습니다.

NOT EXISTS을 이용한 안티 조인 예문 입니다. DEPT 테이블에서 부서코드, 부서명을 출력 하는데  EMP 테이블에 부서원들을 가지고 있지 않은 부서코드, 부서명을 출력합니다. 40번 부서는 EMP 테이블에 해당하는 부서원들이 없습니다. HASH_AJ 힌트구문은 안티 해시조인을 하라는 힌트구문으로 사용하지 않으면 오라클19C에서는 머지조인 안티로 쿼리문을 수행 합니다.

-- EXISTS SELECT deptno, dname FROM    dept WHERE NOT EXISTS    (SELECT /*+ HASH_AJ */ 1     FROM   emp     AND      emp.deptno = dept.deptno)

 

<실행결과>

	DEPTNO	ENAME
1	40	OPERATION

<실행계획(F10)>

#SQL조인, #조인이란, #안티조인, #AntiJoin, #SQL교육, #SQL강의, #오라클교육, #오라클, #오라클조인

오라클 조인방법, 해시 조인(Hash Join)

 

오라클 조인방법, 해시 조인(Hash Join)

해시 조인은 두 테이블 중 한 테이블(Build Input, Driving Table)을 조인키를 기반으로 메모리에 해시테이블을 생성하고 해시테이블 내에 행들을 위치시키기 위해 해시함수를 사용하고 나머지 테이블을 스캔하면서 조인조건을 만족하는 레코드를 찾는 조인방법 입니다.

중첩루프 조인과 같이 조인 시 발생하는 랜덤 액세스에 대한 부하가 없는 조인방식으로 주로 작은 테이블과 큰 테이블의 조인 시 사용 됩니다. 드라이빙 조건과 상관없이 좋은 성능을 발휘할 수 있는 조인 방법이며 대체로 제일 빠른 성능을 보장하므로 비용(COST)이 가장 많이 드는 조인방법 입니다.

해시 조인은 유용한 인덱스가 부족하여 중첩루프 조인이 비효율적 일 때 효과적 일 수 있습니다.

ORDERED 힌트는 FROM절에 나타나는 테이블의 순서대로 왼쪽에서 오른쪽으로 조인을 하라는 힌트이며, USE_HASH 힌트는 HASH JOIN을 하라는 힌트 구문 입니다.

SELECT /*+ ORDERED USE_HASH(E) */                E.empno, E.ename, D.dname FROM    dept D, emp E WHERE D.deptno = E.deptno

 

DEPT 테이블(Build Input, Driving Table)을 먼저 조인키를 기반으로 메모리에 해시테이블을 생성하고 해시테이블 내에 행들을 위치시키기 위해 해시함수를 사용 합니다. 이후 나머지 테이블(Probe Input, Driven Table)을 스캔하면서 조인조건을 만족하는 레코드를 찾는 조인방법 입니다.

<실행 순서>

1. ORDERED 힌트 구문에 따라 DEPT 테이블이 선행 테이블로 메모리에 올라가서 해시 테이블로 만들어 집니다. 2. 후행 테이블 EMP 테이블을 읽어 메모리에 있는 해시 테이블과 조인 키를 비교 합니다. DEPT 테이블의 부서코드(deptno) 값은 10, 20, 30, 40 값을 가지므로 하나씩 EMP 테이블의 deptno 컬럼과 비교하여 조건에 맞는 데이터를 추출 합니다. 해시 조인에서는 선행 테이블을 Build Input, 후행 테이블을 Probe Input 이라고 합니다.

 

해시 조인은 범위 검색(Range scan)이 아닌 등가 비교(Equi-Join, where절에서 등호로 비교하는 경우)에 더 적합 합니다. 선행 테이블인 Build Input이 Hash Area에 담길 정도로 충분히 작아야 하고 Build Input 해시 키 컬럼에 중복 값이 거의 없어야 좋은 성능을 냅니다.

소트 머지 조인하기에는 두 테이블이 너무 커서 소트 부하가 심할 때 유리하며 수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인 할 때 좋습니다. 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조 입니다.​

 

#오라클강좌, #오라클조인, #오라클해시조인, #해시조인, #HashJoin, #SQL #SQL교육, #오라클, #오라클교육, #오라클강의