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(오동규 저)
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
