오라클(Oracle) NOT IN 과 NULL 허용 컬럼

Share

Last Updated on 1월 21, 2024 by Jade(정현호)

 

이번글은 오라클에서 NOT IN 과 NULL 허용 컬럼에 관련된 내용에 알아보도록 하겠습니다.

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

SQL> 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
SFROM EMPLOYEES E)
AND D.LOCATION_ID=1700;


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

SQL> 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 는 부서가 할당되지 않는 상태입니다.

SQL> 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 조건을 추가하면 됩니다.

SQL> 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(오동규 저)

           

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x