Oracle 10g 까지는 NOT IN 서브쿼리를 사용할 때 NULL을 허용하는 컬럼으로 메인쿼리와 조인하면 Anti Join을 사용할 수 없었고 Filter 서브쿼리로 실행되었기 때문에 성능이 저하되었다. 마찬가지로 메인쿼리쪽의 조인컬럼이 NULL 허용이라도 Filter로 처리된다. 하지만 11g부터는 Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. (The Logical Optimizer)에서도 이런 사실을 언급하고 있다. 하지만 Anti Join Null Aware로 인해 변환된 SQL의 모습은 책에서 언급되지 않았으므로 이 글을 통하여 알아보자.

 

먼저 가장 기본적인 예제를 실행해보자.

실행환경: Oracle 11.2.0.1

 

--Anti Join Null Aware를 활성화 시킨다. Default True 이므로 실행하지 않아도 됨.

ALTER SESSION SET "_optimizer_null_aware_antijoin" = TRUE;

 

SELECT d.department_id, d.department_name, location_id

  FROM department d

 WHERE d.department_id NOT IN (SELECT e.department_id

                                 FROM employee e)

   AND d.location_id = 1700;

 

NOT IN 서브쿼리는 두 가지 뜻이 있다

위의 SQL을 해석할 때 단순히 location_id = 1700인 부서 중에서 사원이 한 명도 없는 건을 출력한다고 생각하면 한가지를 놓친 것이다. 만약 이런 요건이라면 NOT IN 대신에 NOT EXISTS 서브쿼리를 사용해야 한다. 다시 말해 NOT IN 서브쿼리를 사용하면 employee 테이블의 department_id 값 중에 한 건이라도 Null이 있으면 결과집합이 출력되지 않는다. 실제로도 결과건수가 없다. 이제 위의 SQL에 해당하는 Plan을 보자.

 

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

| Id  | Operation                     | Name              | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

|*  1 |  FILTER                       |                   |      0 |00:00:00.01 |       7 |

|   2 |   NESTED LOOPS ANTI SNA       |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

|*  4 |     INDEX RANGE SCAN          | DEPT_LOCATION_IX  |      0 |00:00:00.01 |       0 |

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

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

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

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

NULL을 발견하면 멈춘다

NESTED LOOPS ANTI NA라는 기능은 Null 데이터를 찾자마자 Scan을 멈추는 것이다. ID 기준으로 6번의 Predicate Information을 보면 NULL인 데이터를 단 한 건(A-Rows 참조)만 찾아내고 Scan을 멈추었다. 이제 NESTED LOOPS ANTI SNA가 어떻게 수행되는지 10053 Trace를 통하여 살펴보자.

 

FPD: Considering simple filter push in query block SEL$526A7031 (#1)

"D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND  NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "EMPLOYEE" "E")

FPD: Considering simple filter push in query block SUB (#2)

"E"."DEPARTMENT_ID" IS NULL

try to generate transitive predicate from check constraints for query block SUB (#2)

finally: "E"."DEPARTMENT_ID" IS NULL

 

FPD(Filter Push Down) 기능으로 인하여 쿼리블럭명이 SUB Not Exists 서브쿼리가 추가 되었고 그 서브쿼리에 DEPARTMENT_ID IS NULL 조건이 추가되었다.

 

SQL 어떻게 바뀌었나?

위의 10053 Trace 결과에 따르면 Logical Optimizer SQL을 아래처럼 바꾼 것이다.

 

SELECT d.department_id, d.department_name, d.location_id

  FROM department d

 WHERE NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id IS NULL) –-NULL 을 체크하는 서브쿼리

   AND NOT EXISTS (SELECT 0           

                     FROM employee e

                    WHERE e.department_id  = d.department_id)                     

   AND d.location_id = 1700 ;

 

SQL을 보면 NOT IN 서브쿼리가 NOT EXIST 서브쿼리로 바뀌었고 NULL을 체크하는 서브쿼리가 추가되었다. 또한 NULL을 체크하는 서브쿼리의 결과가 한 건이라도 존재하면 SQL은 더 이상 실행되지 않는다는 것을 알 수 있다. NESTED LOOPS ANTI SNA의 비밀이 풀리는 순간이다. ORACLE 9i 10g 에서도 위와 같이 SQL을 작성하면 NESTED LOOPS ANTI SNA의 효과를 볼 수 있다. 하지만 위의 SQL처럼 수동으로 작성하는경우 NULL 한건을 체크 하는데 오래 걸리며 부하가 있다면 이렇게 사용하면 안 된다. 이제 Plan을 보자.

 

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

| Id  | Operation                     | Name              | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT              |                   |      0 |00:00:00.01 |       7 |

|*  1 |  FILTER                       |                   |      0 |00:00:00.01 |       7 |

|   2 |   NESTED LOOPS ANTI           |                   |      0 |00:00:00.01 |       0 |

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |      0 |00:00:00.01 |       0 |

|*  4 |     INDEX RANGE SCAN          | DEPT_LOCATION_IX  |      0 |00:00:00.01 |       0 |

|*  5 |    INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |      0 |00:00:00.01 |       0 |

|*  6 |   TABLE ACCESS FULL           | EMPLOYEE          |      1 |00:00:00.01 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - filter( IS NULL)

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

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

   6 - filter("E"."DEPARTMENT_ID" IS NULL)

 

Operation의 순서에 유의하라

위의 Plan을 과 원본 Plan을 비교해보면 원본이 ANTI SNA라는 것만 제외하면 실행계획과 일량까지 같음을 알 수 있다. 헷갈리지 말아야 할 것은 ID 기준으로 6(NULL 체크 서브쿼리)이 가장 먼저 실행된다는 것이다. 왜냐하면 서브쿼리 내부에 메인쿼리와 조인조건이 없기 때문에 서브쿼리가 먼저 실행될 수 있기 때문이다. 반대로 Filter 서브쿼리내부에 메인쿼리와 조인 조건이 있다면 메인쿼리의 컬럼이 먼저 상수화 되기 때문에 항상 서브쿼리쪽 집합이 후행이 된다. 이런 사실을 모르고 보면 PLAN상으로만 보면 NULL 체크 서브쿼리가 가장 마지막에 실행되는 것으로 착각 할 수 있다.

 

결론

Anti Join Null Aware를 사용하여 Null인 데이터가 한 건이라도 발견되면 Scan을 중단하므로 성능이 향상된다. NULL을 체크하는 Filter 서브쿼리가 추가되기 때문이다. 하지만 그런 서브쿼리가 항상 추가되는 것은 아니다. 추가되는 기준이 따로 있는데 다음 글에서 이 부분을 다루려고 한다.

 

PS

책에 위의 SQL이 빠져있다. SQL PLAN을 출력하여 끼워 넣기 바란다.

Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2010.08.02 10:19  댓글주소  수정/삭제  댓글쓰기

    좋은 내용감사 드립니다
    보통 흔히 아는 실행계획대로 읽는다면 6 - filter("E"."DEPARTMENT_ID" IS NULL) 이부분이 제일 마지막에 필터로 풀린느데 여기서는 이부분이 젤일 먼저 실행되는군요
    이런건 어떻게 판단하는건가요? 트레이스 내용을 보고 판단하는지요? 아니면 SQL을 보고 판단하는건가요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.08.02 16:41 신고  댓글주소  수정/삭제

      안녕하세요. 기린님
      이런 경우는 예외에 속하기 때문에 Plan상의 Operation 부분을 보고 판단할 수 없습니다.
      하지만 SQL을 보면 Uncorreated Subquery(비상관서브쿼리)인지 아닌지 판단할 수 있으므로 어려움은 없을것 입니다..