Oracle/Tuning2019. 10. 17. 19:49

Null-Aware 안티 조인

NOT IN 서브 쿼리의 성능 개선을 위해 10.2.0.6 버전부터 Null-Aware 안티 조인 기능이 추가되었다.


DBA_HIST_PLAN_OPTION_NAME 뷰를 조회해보면 세 가지 유형의 안티 조인을 확인할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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 칼럼이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 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 글을 참조하자.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- 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 안티 조인으로 수행되는 쿼리는 결과가 반환되지 않을 수 있는 잠재적 문제를 가진다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
-- 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 조건을 기술해야 동일한 결과를 얻을 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- 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   |
-----------------------------------


관련 파라미터는 아래와 같다.

1
2
3
4
-- 6
NAME                           DESCRIPTION
------------------------------ -----------------------------
_optimizer_null_aware_antijoin null-aware antijoin parameter


  1. 필터 방식은 NL 조인과 유사하게 동작하므로 적합한 인덱스가 없다면 쿼리의 성능이 저하될 수 있다. [본문으로]
  2. NL 조인이나 소트 머지 조인도 내부 수행 방식은 다르지만 NA, SNA 안티 조인으로 수행될 수 있다. [본문으로]
저작자표시 비영리 변경금지

'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
Posted by 정희락_

티스토리툴바