부정형 조인의 여러 가지 형태와 상황에 따른 효율적인 부정형 조인에 대해서 알아본다.
부분범위 처리를 유지하고 싶은 경우나 서브쿼리가 독자적으로 처리범위를 즐일 수 없을 때는 FILTER형태로 수행하게 하고, 그 외의 경우에는 NOT IN을 사용하여 ANTI-MERGE, ANTI-HASH 방식으로 처리해야 한다.
(KEY WORD : 부정형 조인, ANTI-JOIN, NOT IN, ANTI-HASH, ANTI-MERGE)
- 부정형 조인에는 NOT IN, NOT EXISTS 를 사용하여 조건의 만족 여부를 가려내기 위해서 사용한다. 다음과 같은 부정형으로 데이터를 연결하는 SQL을 살펴보자.
SELECT *
FROM TAB1
WHERE COL1 LIKE 'ABC%'
AND COL2 NOT IN ( SELECT FLD2
FROM TAB2
WHERE FLD3 BETWEEN '19990101' AND '19990131') ;
- 의미상으로 보면 이 SQL은 문제가 없으며 원하는 결과를 얻을수 있다. 그러나 실행계획을 보면 문제점이 나타난다.
SELECT STATEMENT
FILTER
TABLE ACCESS (FULL) OF ‘TAB1’
INDEX (RANGE SCAN) OF COL1_INDEX’ (NON UNIQUE)
TABLE ACCESS (BY ROWID) OF ‘TAB2’
INDEX (RANGE SCAN) OF ‘FLD3_INDEX’ (NON UNIQUE)
- 부정형 조인은 연결고리에 해당하는 TAB1의 COL2와 TAB2의 FLD2가 NOT IN으로 비교되었으므로 이 SUB-QUERY는 논리적으로 결코 제공자의 역할을 할 수 없다. 확인자의 역할을 하는 서브쿼리는 항상 나중에 수행된다. 나중에 수행되는 서브쿼리의 처리주관 인덱스는 연결고리인 FLD2 인덱스가 아니라 FLD3 인덱스가 사용되고 있다.
- 그렇지만 이 말을 메인쿼리가 엑세스한 각각의 로우마다 서브쿼리는 동일한 범위를 중복해서 처리한다는 것을 의미한다. 물론 항상 이렇게 실행계획이 수립되는 것은 아니다. 만약 ‘FLD2+FLD3’로 인덱스가 구성되었거나 FLD3에 인덱스가 없다면 정상적으로 FLD2 인덱스를 사용하는 실행계획이 작성된다. 이와 같이 잘못된 실행계획이 수립되는 것을 방지하기 위해서는 다음과 같이 SQL을 작성해야 한다.
SELECT *
FROM TAB1 X
WHERE COL1 LIKE 'ABC%'
AND NOT EXISTS ( SELECT FLD2
FROM TAB2 Y
WHERE Y.FLD2 = X.COL2
AND Y.FLD3 BETWEEN '19990101' AND '19990131' ) ;
- FILTER 형식으로 처리되는 부정형 조인의 최대 장점은 NESTED LOOPS 조인과 같이 선행 집합에서 요구한 로우들에 대해서만 수행한다는 것이다. 다시 말해서 선행 집합에서 상수값을 제공받아 처리된다는 것이다.
- 또 한가지 장점은 부분범위처리가 가능하다는 것이다. 그러므로, 부분범위처리를 유지하고 싶은 경우나 서브쿼리 독자적으로 처리범위를 제대로 줄일 수 없을 때는 ‘NOT EXISTS’ 를 사용하여 FILTER 형태의 부정형 조인을 사용해야 한다.
- 반면에 메인쿼리에서 추출한 범위가 너무 넓어 서브쿼리가 랜덤으로 처리할 양이 매우 많아 진다고 한다면 위와 같은 방법으로 처리하는 것은 무리가 따른다. 다음의 SQL을 보자
SELECT COUNT(*)
FROM TAB1
WHERE COL1 LIKE 'ABC%'
AND COL2 IS NOT NULL
AND COL2 NOT IN ( SELECT /*+ MERGE_AJ */ FLD2
FROM TAB2
WHERE FLD3 BETWEEN '19990101' AND '19990131'
AND FLD2 IS NOT NULL ) ;
- 위의 SQL을 보면 SORT-MERGE 조인처럼 각각의 집합을 별도로 엑세스하여 정렬시킨 후 연결을 시도하는 갓이 훨씬 유리하다. 비록 부정형으로 연결되었지만 문제될 것이 없다. 머지 단계에서 일반적인 머지와 반대로 ‘머지에 실패한 것’을 추출하기만 하면 동일한 방법이 된다. 실행 계획을 보자.
SELECT STATEMENT Optimizer = FIRST_ROWS
MERGE JOIN (ANTI)
SORT(JOIN)
TABLE ACCESS (BY ROWID) OF 'TAB1'
INDEX (RANGE SCAN) OF 'COL1_INDEX' (NON-UNIQUE)
SORT (UNIQUE)
VIEW
TABLE ACCESS (BY ROWID) OF 'TAB2'
INDEX (RANGE SCAN) OF 'FLD3_INDEX' (NON-UNIQUE)
- 위의 실행계획은 SORT-MERGE 조인과 거의 동일하지만 'MERGE JOIN (ANTI)'로 표시되는 것만 차이가 난다. 이 조인은 반드시 'NOT IN'을 사용한 경우만 가능하며 비용기준 옵티마이져로 정의되어야만 한다.
- 머지 조인이 되는 컬럼은 반드시 NOT NULL이 보장되어야 하므로 각각 IS NOT NULL 조건을 추가한다.
- 해쉬 부정형 조인도 다음과 같이 기술하면 된다.
SELECT COUNT(*)
FROM TAB1
WHERE COL1 LIKE 'ABC%'
AND COL2 IS NOT NULL
AND COL2 NOT IN ( SELECT /*+ HASH_AJ */ FLD2
FROM TAB2
WHERE FLD3 BETWEEN '19990101' AND '19990131'
AND FLD2 IS NOT NULL ) ;
- 더 자세한 내용은 ‘대용량 데이터베이스 솔루션 II page 2-134’를 참조하기 바란다.