NOT IN 서브 쿼리의 성능 개선을 위해 10.2.0.6 버전부터 Null-Aware 안티 조인 기능이 추가되었다.
DBA_HIST_PLAN_OPTION_NAME 뷰를 조회해보면 세 가지 유형의 안티 조인을 확인할 수 있다.
-- 1
SELECT option_id, option_name
FROM dba_hist_plan_option_name
WHERE option_name LIKE 'ANTI%'
AND option_name NOT LIKE '%BUFFERED';
OPTION_ID OPTION_NAME
--------- -----------
2 ANTI
124 ANTI NA
125 ANTI SNA
3 행이 선택되었습니다.
테스트를 위해 아래와 같이 테이블을 생성하자. c1은 NULLLABLE 칼럼이다.
-- 2 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE; CREATE TABLE t1 (c1 NUMBER); CREATE TABLE t2 (c1 NUMBER); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (NULL); INSERT INTO t2 VALUES (2); INSERT INTO t2 VALUES (NULL); COMMIT;
아래는 10.2.0.5 버전의 실행계획이다. 3-1번 쿼리처럼 NOT IN 조건에 사용한 열이 모두 널을 포함하지 않아야 안티 조인으로 수행되고, 3-2, 3-3, 3-4번 쿼리처럼 하나의 열이라도 널을 포함할 수 있다면 필터 방식으로 수행되는 것을 확인할 수 있는데 이로 인해 쿼리의 성능이 저하될 수 있었다.1 성능 비교는 http://structureddata.org/2008/05/22/null-aware-anti-join 글을 참조하자.
-- 3-1
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.5') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2 WHERE c1 IS NOT NULL)
AND c1 IS NOT NULL;
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI | |
|* 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 3-2
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.5') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2);
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 3-3
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.5') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2)
AND c1 IS NOT NULL;
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
|* 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 3-4
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.5') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2 WHERE c1 IS NOT NULL);
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | FILTER | |
| 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
아래 쿼리는 10.2.0.6 버전의 실행계획이다. 4개의 쿼리 모두 안티 조인으로 수행된 것을 확인할 수 있다. 4-2, 4-3번 쿼리처럼 서브 쿼리의 열이 널을 포함할 수 있다면 Null-Aware 안티 조인, 4-4번 쿼리처럼 메인 쿼리의 열만 널을 포함할 수 있다면 Single Null-Aware 안티 조인으로 수행된다.2 여기서 중요한 점은 안티 조인의 수행 방식이 아니라 NA 안티 조인으로 수행된 4-2, 4-3번 쿼리의 결과가 반환되지 않았다는 것이다. NA 안티 조인으로 수행되는 쿼리는 결과가 반환되지 않을 수 있는 잠재적 문제를 가진다.
-- 4-1
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.6') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2 WHERE c1 IS NOT NULL)
AND c1 IS NOT NULL;
C1
--
1
1개의 행이 선택되었습니다.
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI | |
|* 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 4-2
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.6') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2);
선택된 레코드가 없습니다.
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI NA | | -- Null-Aware
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 4-3
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.6') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2)
AND c1 IS NOT NULL;
선택된 레코드가 없습니다.
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI NA | | -- Null-Aware
|* 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
-- 4-4
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('10.2.0.6') */
*
FROM t1
WHERE c1 NOT IN (SELECT /*+ UNNEST HASH_AJ */ c1 FROM t2 WHERE c1 IS NOT NULL);
C1
--
1
1개의 행이 선택되었습니다.
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN ANTI SNA| | -- Single Null-Aware
| 2 | TABLE ACCESS FULL| T1 |
|* 3 | TABLE ACCESS FULL| T2 |
-----------------------------------
해법은 단순하다. NOT IN 조건 대신 NOT EXISTS 조건을 사용하면 된다. 아래는 NOT EXISTS 조건을 사용한 쿼리다. 두 쿼리 모두 안티 조인으로 수행된 것을 확인할 수 있다. SNA 안티 조인인 경우 5-2번 쿼리처럼 메인 쿼리에 NOT NULL 조건을 기술해야 동일한 결과를 얻을 수 있다.
-- 5-1 SELECT * FROM t1 a WHERE NOT EXISTS (SELECT /*+ UNNEST HASH_AJ */ 1 FROM t2 x WHERE x.c1 = a.c1); C1 -- 1 2 행이 선택되었습니다. ----------------------------------- | Id | Operation | Name | ----------------------------------- | 0 | SELECT STATEMENT | | |* 1 | HASH JOIN ANTI | | | 2 | TABLE ACCESS FULL| T1 | | 3 | TABLE ACCESS FULL| T2 | ----------------------------------- -- 5-2 SELECT * FROM t1 a WHERE NOT EXISTS (SELECT /*+ UNNEST HASH_AJ */ 1 FROM t2 x WHERE x.c1 = a.c1) AND a.c1 IS NOT NULL; C1 -- 1 1개의 행이 선택되었습니다. ----------------------------------- | Id | Operation | Name | ----------------------------------- | 0 | SELECT STATEMENT | | |* 1 | HASH JOIN ANTI | | |* 2 | TABLE ACCESS FULL| T1 | | 3 | TABLE ACCESS FULL| T2 | -----------------------------------
관련 파라미터는 아래와 같다.
-- 6 NAME DESCRIPTION ------------------------------ ----------------------------- _optimizer_null_aware_antijoin null-aware antijoin parameter
'Oracle > Tuning' 카테고리의 다른 글
| OUTER OR JOIN 조건 #2 (0) | 2019.10.28 |
|---|---|
| V$DIAG_TRACE_FILE_CONTENT 뷰 (0) | 2019.10.18 |
| OPTIMIZER_FEATURES_ENABLE (1) | 2019.10.17 |
| PQ_FILTER 힌트 (0) | 2019.10.14 |
| 누적합 계산 (0) | 2019.10.14 |
