메뉴 건너뛰기

bysql.net

2.22_ANTI_NA(Anti_Join_Null_Aware)

2011.10.10 20:15

AskZZang 조회 수:2501

2.22 ANTI NA (Anti Join Null Aware)

 

Oracle 10g 까지

           Not In 서브쿼리 사용시에 Anti Join으로 풀리지 않으며 조인 컬럼을 Is Not Null

           명시해야만 Anti Join으로 풀릴 수 있다.

 

Oracle 11g 부터

           Anti Join의 메커니즘이 향상되어 조인이 되는 컬럼이 Null 허용 컬럼이라 하더라도 Anti

           Join이 가능해졌다.

 

 

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';                  

                 

SELECT /*+ QB_NAME(MAIN) */

       d.department_id, d.department_name, location_id

  FROM departments d

 WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */

                                      e.department_id

                                 FROM employees e)

   AND d.location_id = 1700;

 

----------------------------------------------------------+-----------------------------------+

| Id  | Operation                      | Name             | Rows  | Bytes | Cost  | Time      |

----------------------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT               |                  |       |       |     6 |           |

| 1   |  FILTER                        |                  |       |       |       |           |

| 2   |   NESTED LOOPS ANTI SNA        |                  |    10 |   220 |     6 |  00:00:01 |

| 3   |    TABLE ACCESS BY INDEX ROWID | DEPARTMENTS      |    21 |   399 |     2 |  00:00:01 |

| 4   |     INDEX RANGE SCAN           | DEPT_LOCATION_IX |    21 |       |     1 |  00:00:01 |

| 5   |    INDEX RANGE SCAN            | EMP_DEPARTMENT_IX|    56 |   168 |     0 |           |

| 6   |   TABLE ACCESS FULL            | EMPLOYEES        |     1 |     3 |     4 |  00:00:01 |

----------------------------------------------------------+-----------------------------------+

 

Predicate Information:

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

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)

 

 

# _optimizer_null_aware_antijoin 파라미터

  : Null 허용 컬럼에 대하여 Not In 으로 조인할 경우에도 Anti Join을 사용할 수 있도록 control

   하는 파라미터

 

# NESTED LOOPS ANTI SNA

  : SNA Single Null-Aware Anti Join의 약자이다.

   이것은 조인되는 컬럼 중에 한쪽만 Null 허용 컬럼이라는 뜻이다.

 

 

# Plan 상의 Predicate Information 1번에서 Filter(IS NULL) 조건이 추가되었다.

 

********************************

COST-BASED QUERY TRANSFORMATIONS

********************************

FPD: Considering simple filter push (pre rewrite) in query block SUB (#0)

FPD: Current where clause predicates ??

FPD: Considering simple filter push (pre rewrite) in query block MAIN (#0)

FPD: Current where clause predicates "D"."DEPARTMENT_ID"<>ALL (SELECT /*+ QB_NAME ("SUB") */ "E"."DEPARTMENT_ID" FROM "EMPLOYEES" "E") AND "D"."LOCATION_ID"=1700*****************************

Cost-Based Subquery Unnesting

*****************************

SU: Unnesting query blocks in query block MAIN (#1) that are valid to unnest.

Subquery removal for query block SUB (#2)

RSW: Not valid for subquery removal SUB (#2)

Subquery unchanged.

Subquery Unnesting on query block MAIN (#1)SU: Performing unnesting that does not require costing.

SU: Considering subquery unnest on query block MAIN (#1).

SU:   Checking validity of unnesting subquery SUB (#2)

SU:   Passed validity checks.

SU:   Transform ALL subquery to a null-aware antijoin.

Registered qb: SEL$526A7031 0xaa0dd8 (SUBQUERY UNNEST MAIN; SUB)

# Trace 내용을 분석해보면 Simple Subquery인 경우는 Anti Join과 마찬가지로 Null Aware Anti Join도 비용계산

과정이 필요치 않음을 알 수 있다.

또한 Not In 서브쿼리가 먼저 NOT = ALL 서브쿼리로 바뀌었으며 다시 Null Aware Anti Join으로 변환 되었음을

알 수 있다.

 

 

************************************

Cost-based predicate pushdown (JPPD)

************************************

….

….

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

finally: "E"."DEPARTMENT_ID" IS NULL

 

try to generate transitive predicate from check constraints for query block SEL$526A7031 (#1)

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

 

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT /*+ QB_NAME ("MAIN") */ "D"."DEPARTMENT_ID" "DEPARTMENT_ID","D"."DEPARTMENT_NAME" "DEPARTMENT_NAME","D"."LOCATION_ID" "LOCATION_ID" FROM "HR"."EMPLOYEES" "E","HR"."DEPARTMENTS" "D" WHERE "D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=1700 AND  NOT EXISTS (SELECT /*+ QB_NAME ("SUB") */ 0 FROM "HR"."EMPLOYEES" "E" WHERE "E"."DEPARTMENT_ID" IS NULL)

 

 

# 추가적인 Trace 정보를 해석해 보면 쿼리블럭 SUB(#2) E.DEPARTMENT_ID IS NULL 조건이 추가되었으며

Not In 서브쿼리가 Not Exists 서브쿼리로 변환된 것을 알 수 있다.

IS NULL 조건이 추가된 이유는 서브쿼리쪽의 조인컬럼이 Null인 데이터를 먼저 찾아서 Scan을 멈추기

위함이다.

Not In 서브쿼리는 서브쿼리쪽의 데이터 중에 조인 컬럼이 Null인 데이터가 한 건이라도 있으면 결과가 나오지

않는다. 따라서 Null인 데이터를 찾자마자 Scan을 멈출 수 있는 것이다.

 

 

모든 Not In 서브쿼리에 IS NULL 조건을 추가하면 결과가 틀려지지 않을까?

 

SELECT /*+ QB_NAME(MAIN) */

       d.department_id, d.department_name, location_id

  FROM departments d

 WHERE d.department_id NOT IN (SELECT /*+ QB_NAME(SUB) */

                                      e.department_id

                                 FROM employees e

WHERE e.job_id = ‘PU_CLERK’)

   AND d.location_id = 1700;

 

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

| Id  | Operation                  | Name            | Rows  | Bytes | Cost (%CPU)| Time   |

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

|   0 | SELECT STATEMENT           |                 |    16 |   496 |     5  (20)| 00:00:01 |

|*  1 |  HASH JOIN ANTI NA         |                 |    16 |   496 |     5  (20)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |    21 |   399 |     2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN        | DEPT_LOCATION_IX |    21 |     |     1   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     5 |    60 |     2   (0)| 00:00:01 |

|*  5 |    INDEX RANGE SCAN        | EMP_JOB_IX      |     5 |      |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

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

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

   5 - access("E"."JOB_ID"='PU_CLERK')

 

 

# 서브쿼리의 조건절에 e.job_id = ‘PU_CLERK’ 조건츨 추가하자 IS NULL FILTER가 사라졌다.

이처럼 서브쿼리의 조건절이 추가된다면 그에 따라 적응적 탐색(Adaptive Null Aware Scan)을 하므로 걱정할

것이 없다.

 

# Oracle11g에서 Null Aware Anti Join 기능이 추가된 진정한 이유는 무엇일까?

  그 이유는 당연히 성능관점에 있다.

 

 

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

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

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

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

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

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

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

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

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

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

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

 

Plan 상에는 Departments 부터 Scan한 것으로 나타나지만 내부적으로 EMPLOYYEES 쪽의 조인컬럼이 Null

데이터를 찾자마자 Scan을 멈춘것이다. 왜냐하면 Not In 서브쿼리의 조인컬럼 중에 하나라도 Null이면

전체쿼리가 False가 되어 버리기 때문에 나머지 테이블들을 Scan할 필요가 없기 때문이다.

A-Rows 항목을 보면 더욱 명확해진다.

EMPLOYEES쪽만 1 Scan 되었고 나머지 쪽은 Scan한 것이 없다. 대단하지 않은가.!!!!

오라클의 Anti Join 메커니즘이 점점 똑똑해져 가고 있다.