NOT EXISTS 서브 쿼리가 UNNEST되지 않는 사례

NOT EXISTS 서브 쿼리의 SELECT 절에 메인 쿼리의 열을 기술하면 서브 쿼리가 UNNEST되지 않는다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 10000');
CREATE TABLE t2 AS SELECT * FROM t1;

2-1번 쿼리는 NOT EXISTS 서브 쿼리의 SELECT 절에 메인 쿼리의 열(a.c1)을 기술했다. 서브 쿼리가 UNNEST되지 못하고 필터 방식으로 처리되는 것을 확인할 수 있다. 이런 경우 쿼리의 성능이 저하될 수 있다. 2-2번 쿼리처럼 리터럴(1)을 기술하면 서브 쿼리가 UNNEST되어 안티 조인 방식으로 조인된다.

-- 2-1
SELECT *
  FROM t1 a
 WHERE NOT EXISTS (SELECT /*+ UNNEST HASH_AJ */
                          a.c1 -- !
                     FROM t2 x
                    WHERE x.c1 = a.c1);

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  FILTER            |      |
|   2 |   TABLE ACCESS FULL| T1   |
|*  3 |   TABLE ACCESS FULL| T2   |
-----------------------------------

-- 2-2
SELECT *
  FROM t1 a
 WHERE NOT EXISTS (SELECT 1
                     FROM t2 x
                    WHERE x.c1 = a.c1);

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|*  1 |  HASH JOIN RIGHT ANTI|      |
|   2 |   TABLE ACCESS FULL  | T2   |
|   3 |   TABLE ACCESS FULL  | T1   |
-------------------------------------

EXISTS 서브 쿼리는 SELECT 절에 메인 쿼리의 열을 기술해도 세미 조인 방식으로 조인된다. 하지만 3-2번 쿼리처럼 리터럴을 기술하는 편이 바람직하다.

-- 3-1
SELECT *
  FROM t1 a
 WHERE EXISTS (SELECT a.c1 -- !
                 FROM t2 x
                WHERE x.c1 = a.c1);

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|*  1 |  HASH JOIN RIGHT SEMI|      |
|   2 |   TABLE ACCESS FULL  | T2   |
|   3 |   TABLE ACCESS FULL  | T1   |
-------------------------------------

-- 3-2
SELECT *
  FROM t1 a
 WHERE EXISTS (SELECT 1
                 FROM t2 x
                WHERE x.c1 = a.c1);

-------------------------------------
| Id  | Operation            | Name |
-------------------------------------
|   0 | SELECT STATEMENT     |      |
|*  1 |  HASH JOIN RIGHT SEMI|      |
|   2 |   TABLE ACCESS FULL  | T2   |
|   3 |   TABLE ACCESS FULL  | T1   |
-------------------------------------