[Oracle] unnest, no_unnest

unnest : 서브쿼리를 unnesting 함으로써 join방식으로 최적화하도록 유도

no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로처리

drop table t1;
drop table t2;

create table t1(c1 number, c2 number);
create table t2(c1 number, c2 number);

insert into t1 select level, level from dual connect by level <= 100;
insert into t2 select level, level from dual connect by level <= 1000;
commit;

 

기존쿼리

SELECT *
FROM t1
WHERE t1.c1 IN (SELECT c1 FROM t2);

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  3900 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  3900 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |  2600 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="C1")

 

unnest hint 사용

-- subquery에 unnest hint를 사용 hint를 사용하지 않은 쿼리와 동일
SELECT *
FROM t1
WHERE t1.c1 IN (SELECT /*+ unnest */ c1 FROM t2);

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  3900 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |   100 |  3900 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |   100 |  2600 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 | 13000 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."C1"="C1")

 

no_unnest hint 사용

-- subquery에 no_unnest hint를 사용하여 subquery를 filter 동작방식으로 처리
SELECT *
FROM t1
WHERE t1.c1 IN (SELECT /*+ no_unnest */ c1 FROM t2);

100 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    32 |   832 |     9   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |   100 |  2600 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |    10 |   130 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "C1"=:B1))
   3 - filter("C1"=:B1)

• 옵티마이저가 서브쿼리 Unnesting을 선호하므로 no_unnest 힌트 사용
• 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리됨("C1"=:B1)
• 이것을 통해 서브쿼리를 별도로 최적화한다는 것을 알 수 있음
• Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복 수행함.