Subquery Unnesting을 우리 말로 해석하면 "Subquery를 둘러싼 둥지(Nest)를 풀겠다(Un)"는 것이다
Subquery Unnesting이 성공적으로 이루어지면 Subquery는 대부분의 경우 Join으로 변환된다
_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이라는 예약어는 존재하지 않는다.(가상의 표현)
Unnesting 된 Subquery는 대부분의 경우 Semi Join(In 이나 Exists), Anti Join(Not IN 이나 Not Exists), 일반 Join등으로 변환된다.
변환되고 나면 보다 넓은 관점에서 최적의 Join 방식(Hash, Nested Loop, Sort Merge)이나
Access 방식(Index Range Scan, Index Fast Full Scan, Table Full Scan, ...)을 가지도록
실행 계획을 수립할 수 있다.