RSS구독하기:SUBSCRIBE TO RSS FEED
즐겨찾기추가:ADD FAVORITE
글쓰기:POST
관리자:ADMINISTRATOR
'ANTI JOIN'에 해당되는 글 1
2012/04/21  NOT IN 과 NULL 허용 컬럼  


테스트 환경 : 11gR2

현재 버전이 11gR2 임으로 _OPTIMIZER_NULL_AWARE_ANTIJOIN 파라미터를 FALSE 로 변경 하고 진행한다

ALTER SESSION SET "_OPTIMIZER_NULL_AWARE_ANTIJOIN"=FALSE;

employees 테이블의 department_id null 허용 컬럼이다.

SELECT /*+ GATHER_PLAN_STATISTICS */D.DEPARTMENT_ID,
D.DEPARTMENT_NAME, LOCATION_ID
  FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID NOT IN(SELECT E.DEPARTMENT_ID
 
FROM EMPLOYEES E)
AND D.LOCATION_ID=1700;


쿼리 수행후 dbms_xplan 을 이용하여 실행계획을 확인 해본다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'ALLSTATS LAST -ROWS +PREDICATE'));

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 0 |00:00:00.01 | 86 |
|* 1 | FILTER | | 1 | 0 |00:00:00.01 | 86 |
|  2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 21 |00:00:00.01 | 1 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 21 | 21 |00:00:00.01 | 84 |
--------------------------------------------------------------------------------------------------




ANTI JOIN(AJ) 로 수행되지 않고 FILTER 로 수행 되고 있는 것을 확인 할 수 있다.

ANTI JOIN 에 대해서 여러 블로그나 문서에서 많이 기술되어 있음으로 찾아보길 바란다.

 

 

ANTI JOIN 으로 풀리지 않고 FILTER 로 풀리는것 보다 더 이상한것은 실제 SQL을 수행하면 단 한건의

결과도 나오지 않는 다는 것이다.

 

 

먼저 ANTI JOIN 대신 FILTER 로 풀리는 이유는 NOT IN 서브쿼리를 사용 할 때 조인 되는 컬럼인

E.DEPARTMENT_ID NULL 허용 컬럼 이라는 것이 이유 이며, 10g까지는 NULL 허용이라도 ANTI JOIN 으로

풀지 못할 이유가 없는데도 ANTI JOIN 이 되지 않는다.(11g 부터 가능)




그 다음 그렇다면 SQL 수행시 결과가 한 건도 나오지 않은 이유는 아래와 같다.

 

 

EMPLOYEES 테이블의 DEPARTMENT_ID NULL 허용 컬럼 이며, 실제로 NULL ROW 가 존재 한다

사원번호 178 Kimberely 는 부서가 할당 되지 않는 상태이다.

( SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=178; )

 

 

 

예를 들어 DEPARTMET_ID 의 결과가 10,20,NULL 이라고 가정 하고 NOT IN 을 사용하면

 

WHERE DEPARTMENT_ID NOT IN (10,20,NULL) 의 연산을 논리적으로 풀면 아래와 같다

=> WHERE NOT (DEPARTMENT_ID=10 OR DEPARTMENT_ID=20 OR DEPARTMENT=NULL)

 

 

위의 연산에서 NOT 을 제거 하는 연산으로 변경 하면 아래와 같다.

WHERE DEPARTMENT_ID <> 10

AND DEPARTMENT_ID <> 20

AND DEPARTMENT_ID <> NULL

 

 

 

마지막의 DEPARTMENT_ID <> NULL 에서 보면 NULL을 비교 하고 있으며,
논리적으로
NULL 은 비교 할수 없는 연산이다

따라서 에러는 발생하지 않지만
NULL 과 비교 할 수 없기 때문에
결과 건수가 하나도 없는 것이다
.

 

 

 

 

위와 같이 NOT IN 연산자를 사용 할 때 항상 주의 해야 하며, 원하는 결과를 얻으려면 IS NOT NULL 조건을

추가 하면 된다.

 

 

 

SELECT /*+ GATHER_PLAN_STATISTICS */

D.DEPARTMENT_ID, D.DEPARTMENT_NAME, LOCATION_ID

FROM DEPARTMENTS D

WHERE D.DEPARTMENT_ID NOT IN(SELECT E.DEPARTMENT_ID

FROM EMPLOYEES E

WHERE E.DEPARTMENT_ID IS NOT NULL )

AND D.LOCATION_ID=1700;

 

 

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS ANTI | | 1 | 16 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 21 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 21 | 5 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):

---------------------------------------------------

 3 - access("D"."LOCATION_ID"=1700)

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

filter("E"."DEPARTMENT_ID" IS NOT NULL)

 

 

FILTER 대신 NL ANTI JOIN 으로 변경 되었으며, 결과도 추출 될 것이다.

 

논리상 NULL 이 비교 대상이 되게 되면 NULL은 비교 대상이 아니기 때문에 원하는 결과를 추출 할 수 없으므로

주의 해야 한다.

 

 

 

NOT IN 대신 NOT EXISTS 를 사용 한다면 이러한 걱정을 하지 않아도 된다.

 

NULL 과 비교를 할 경우 비교가 불가능 하게 되므로 서브쿼리의 결과가 RETURN 되지 않는다.

 

NOT EXISTS 의 원리는 메인 쿼리에서 받은 컬럼의 값으로 서브쿼리와 조인한 결과가 공집합일 경우에만

TRUE가 된다는 것이기 때문이다.

 

그러므로 NOT IN 을 써야 할 때 에는 서브쿼리 컬럼의 NOT NULL 유무 확인 및 IS NOT NULL 을 확인 해야 할 것이다.

 

 


참조 : THE LOGICAL OPTIMIZER(오동규 저)




페도라 한국 사용자 모임

페도라 한국 사용자 모임 태랑의 포스팅글입니다
  파이어폭스에 최적화 되어있습니다
2012/04/21 06:24 2012/04/21 06:24

이 포스트가 유용하셨다면 구독하세요

http://www.commit.co.kr/trackback/117
태랑:Extreme Performance DBA 를 꿈꾼다
admin@commit.co.kr / 
purityboy83@gmail.com
Extreme Performance DBA 를 꿈꾼다 admin@commit.co.kr / purityboy83@gmail.com
전체보기 (112)
Solaris (14)
Linux (45)
DATABASE (32)
Enterprise Manager (4)
Virtualization (0)
JAVA / C / 그외 개발언어 (3)
넓은 세상의 이야기들 (14)

Tag List