Subquery Unnesting

• Subquery Unnesting을 우리 말로 해석하면 "Subquery를 둘러싼 둥지(Nest)를 풀겠다(Un)"는 것이다
• Subquery Unnesting이 성공적으로 이루어지면 Subquery는 대부분의 경우 Join으로 변환된다

Simple Example

_OPTIMIZER_COST_BASED_TRANSFORMATION

• Subquery Unnesting이나 View Merging 등의 Query Transformation을 Cost based로 수행할 지의 여부를 지정한다.
• Cost Based Query Transformation은 10g에서 소개된 개념으로 기본값은 항상 True이다.
• Oracle 9i까지는 Oracle은 가능한 항상 Query Transformation을 시도한다.
• 하지만 10g부터는 비용(Cost)를 고려하여 Subquery Unnesting과 View Merging이 비용을 줄이는데 도움이 될 때만 수행한다.

_OPTIMIZER_PUSH_PRED_COST_BASED

• Push Predicate을 Cost based로 수행할 지의 여부를 지정한다.
• Cost Based Push Predicate은 10g에서 소개된 개념으로 기본값은 항상 True이다.
• Oracle 9i까지는 Oracle은 가능한 항상 Push Predicate을 시도한다.
• 하지만 10g부터는 비용(Cost)를 고려하여 Push Predicate의 발생이 비용을 줄이는데 도움이 될 때만 Push Predicate을 수행한다.

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;

create table t1(c1 int, c2 char(10), c3 int);
create table t2(c1 int, c2 char(10), c3 int);
create table t3(c1 int, c2 char(10), c3 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

-- Cost Based Query Transformation 비활성화
alter session set "_optimizer_cost_based_transformation" = off;
alter session set "_optimizer_push_pred_cost_based" = false;

--Column c1 : 1~10000의 Distinct Count
--Column c2 : 'dummy' 하나의 값
--Column c3 : 1~10의 Distinct Count

insert into t1
select level, 'dummy', mod(level, 10) + 1 from dual
connect by level <= 10000
;

insert into t2
select level, 'dummy', mod(level, 10) + 1 from dual
connect by level <= 1000
;

insert into t3
select level, 'dummy', mod(level, 10) + 1 from dual
connect by level <= 100
;

commit;

@gather t1
@gather t2
@gather t3

• NO_UNNEST Hint를 사용해 Subquery Unnesting이 이루어지지 않도록 지정.
• Subquery Unnesting이 이루어지지 않았기 때문에 Subquery는 Join으로 변환되지 못한다.
• 따라서 In Operation을 처리할 수 있는 유일한 방법은 Filter Operation을 사용하는 것이다.

select count(*) 
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select /*+ no_unnest */ t2.c1 from t2)
)
;

@stat
----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE     |       |      1 |      1 |            |      1 |00:00:00.17 |   20038 |
|*  2 |   FILTER            |       |      1 |        |            |   1000 |00:00:00.03 |   20038 |
|   3 |    TABLE ACCESS FULL| T1    |      1 |  10000 |    11   (0)|  10000 |00:00:00.04 |      38 |
|*  4 |    INDEX RANGE SCAN | T2_N1 |  10000 |      1 |     1   (0)|   1000 |00:00:00.12 |   20000 |
----------------------------------------------------------------------------------------------------

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

   2 - filter( IS NOT NULL)
   4 - access("T2"."C1"=:B1)

- Table t1에서 Row를 하나씩 추출하면서(3번 단계)
- Index t2_n1(4번 단계)에 대해 값을 비교(2번 단계)한다.


- 8i 이전버전에서는 In Operation에 대해 항상 Filter Operation이 사용되었다.
- Filter Operation은 매우 비효율적이어서(효율적인 면도 있음) Logical Reads가 20,038 Block에 이르는 것을 알 수 있다.
- Subquery Unnesting이 이루어지지 않았을 때의 가장 큰 문제는 Optimizer가 취할 수 있는 선택의 폭이 극단적으로 제한된다는 것이다.

select count(*) 
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
)
;

@stat

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE        |       |      1 |      1 |            |      1 |00:00:00.01 |      31 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI |       |      1 |    999 |    10  (10)|   1000 |00:00:00.02 |      31 |  1517K|  1517K| 1434K (0)|
|   3 |    INDEX FAST FULL SCAN| T2_N1 |      1 |   1000 |     2   (0)|   1000 |00:00:00.01 |       7 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| T1_N1 |      1 |  10000 |     7   (0)|  10000 |00:00:00.03 |      24 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."C1"="T2"."C1")


- Table t1과 t2에 대한 Hash Join이 이루어졌음을 알 수 있다.
- Table Full Scan과 Index Rang Scan 대신 Index Fast Full Scan이 사용됨으로써 일량이 극적으로 줄었다.

select count(*)
from
(
select *
from t1 semi join t2
where t1.c1 = t2.c1
)
;


- Oracle이 원래 SQL 문장을 다음과 같은 가상의 SQL 문장으로 변환했다는 것이다.
- 물론 Semi Join이라는 예약어는 존재하지 않는다.(가상의 표현)