• 휴리스틱 기반의 쿼리 변환 : 특정 조건을 충족시키는 경우 적용
• 비용 기반의 쿼리 변환 : 비용 추정기가 산출한 비용에 근거하여, 원래 실행 계획보다 더 낮은 비용의 실행 계획이 생성될 경우 적용

쿼리 변환 - 서브쿼리 통합 (휴리스틱 기반)

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$ACD206C8 (#2)
SU:   Passed validity checks.
SU:   Transforming EXISTS subquery to a join.
Registered qb: SEL$82F4A621 0x715d9c90 (SUBQUERY UNNEST SEL$1; SEL$ACD206C8)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$82F4A621 nbfros=2 flg=0
    fro(0): flg=0 objn=148979 hint_alias="T1"@"SEL$1"
    fro(1): flg=0 objn=148981 hint_alias="T2"@"SEL$2"

*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in query block SEL$82F4A621 (#1) that are valid to merge.
OJE: Begin: find best directive for query block SEL$82F4A621 (#1)
OJE: End: finding best directive for query block SEL$82F4A621 (#1)
kkqctdrvTD-cleanup: transform(in-use=5304, alloc=8136) :
    call(in-use=3784, alloc=16344), compile(in-use=151256, alloc=177944), execution(in-use=213368, alloc=215968)

kkqctdrvTD-end:
    call(in-use=3784, alloc=16344), compile(in-use=143496, alloc=177944), execution(in-use=213368, alloc=215968)

SU:   Transforming EXISTS subquery to a join.
SJC: Considering set-join conversion in query block SEL$82F4A621 (#1)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
OJE: Begin: find best directive for query block SEL$82F4A621 (#1)
OJE: End: finding best directive for query block SEL$82F4A621 (#1)
JE:   Considering Join Elimination on query block SEL$82F4A621 (#1)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
JE:   cfro: T2 objn:148979 col#:1 dfro:T1 dcol#:1
JE:   cfro: T2 objn:148979 col#:1 dfro:T1 dcol#:1
JE:   cfro: T1 objn:148981 col#:1 dfro:T2 dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
Query block SEL$82F4A621 (#1) unchanged
PM: Considering predicate move-around in query block SEL$82F4A621 (#1)
**************************
Predicate Move-Around (PM)
**************************
PM:     PM bypassed: Outer query contains no views.
PM:     PM bypassed: Outer query contains no views.
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
    call(in-use=4760, alloc=16344), compile(in-use=144368, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=4760, alloc=16344), compile(in-use=145160, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-end:
    call(in-use=4760, alloc=16344), compile(in-use=145640, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
    call(in-use=4760, alloc=16344), compile(in-use=145640, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=4760, alloc=16344), compile(in-use=146408, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-end:
    call(in-use=4760, alloc=16344), compile(in-use=146888, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
    call(in-use=4760, alloc=16344), compile(in-use=146888, alloc=177944), execution(in-use=213408, alloc=215968)

TE: Checking validity of table expansion for query block SEL$82F4A621 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=4760, alloc=16344), compile(in-use=147680, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-end:
    call(in-use=4760, alloc=16344), compile(in-use=148160, alloc=177944), execution(in-use=213408, alloc=215968)

TE: Checking validity of table expansion for query block SEL$82F4A621 (#1)
TE: Bypassed: No partitioned table in query block.
ST: Query in kkqstardrv:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
    call(in-use=4904, alloc=16344), compile(in-use=148160, alloc=177944), execution(in-use=213408, alloc=215968)

JF: Checking validity of join factorization for query block SEL$82F4A621 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=4904, alloc=16344), compile(in-use=148928, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-end:
    call(in-use=4904, alloc=16344), compile(in-use=149408, alloc=177944), execution(in-use=213408, alloc=215968)

JPPD:  Considering Cost-based predicate pushdown from query block SEL$82F4A621 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$82F4A621 (#1)
kkqctdrvTD-start: :
    call(in-use=4904, alloc=16344), compile(in-use=149408, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=4904, alloc=16344), compile(in-use=150176, alloc=177944), execution(in-use=213408, alloc=215968)

kkqctdrvTD-end:
    call(in-use=4904, alloc=16344), compile(in-use=150656, alloc=177944), execution(in-use=213408, alloc=215968)

JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$82F4A621 (#1)
FPD: Considering simple filter push in query block SEL$82F4A621 (#1)
"T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
try to generate transitive predicate from check constraints for query block SEL$82F4A621 (#1)
finally: "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T2"."ID"="T1"."ID" AND ("T2"."N">10 OR "T2"."N"<100)
kkoqbc: optimizing query block SEL$82F4A621 (#1)

        :
    call(in-use=5240, alloc=16344), compile(in-use=151960, alloc=177944), execution(in-use=213408, alloc=215968)

kkoqbc-subheap (create addr=0x7f7752695618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.n > 10 AND t2.id = t1.id)
OR EXISTS (SELECT 1 FROM t2 WHERE t2.n < 100 AND t2.id = t1.id)

SELECT "T1"."ID" "ID",
       "T1"."N" "N",
       "T1"."PAD" "PAD"
  FROM "CHRIS"."T2" "T2",
       "CHRIS"."T1" "T1"
 WHERE "T2"."ID"="T1"."ID"
   AND ("T2"."N">10 OR "T2"."N"<100)