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

쿼리 변환 - 서브쿼리 UNNESTING (휴리스틱, 비용 기반)

*************************
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"."PAD" IS NOT NULL
JE:   cfro: T2 objn:148971 col#:1 dfro:T1 dcol#:1
JE:   cfro: T2 objn:148971 col#:1 dfro:T1 dcol#:1
JE:   cfro: T1 objn:148973 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"."PAD" IS NOT NULL
Query block SEL$5DA710D3 (#1) unchanged
PM: Considering predicate move-around in query block SEL$5DA710D3 (#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$5DA710D3 (#1)
kkqctdrvTD-start: :
    call(in-use=3424, alloc=16344), compile(in-use=138376, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=3424, alloc=16344), compile(in-use=139008, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-end:
    call(in-use=3424, alloc=16344), compile(in-use=139328, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
    call(in-use=3424, alloc=16344), compile(in-use=139328, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=3424, alloc=16344), compile(in-use=139936, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-end:
    call(in-use=3424, alloc=16344), compile(in-use=140256, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
    call(in-use=3424, alloc=16344), compile(in-use=140256, alloc=173928), execution(in-use=204248, alloc=207856)

TE: Checking validity of table expansion for query block SEL$5DA710D3 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=3424, alloc=16344), compile(in-use=140864, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-end:
    call(in-use=3424, alloc=16344), compile(in-use=141184, alloc=173928), execution(in-use=204248, alloc=207856)

TE: Checking validity of table expansion for query block SEL$5DA710D3 (#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"."PAD" IS NOT NULL
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
    call(in-use=3520, alloc=16344), compile(in-use=141184, alloc=173928), execution(in-use=204248, alloc=207856)

JF: Checking validity of join factorization for query block SEL$5DA710D3 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=3520, alloc=16344), compile(in-use=141808, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-end:
    call(in-use=3520, alloc=16344), compile(in-use=142128, alloc=173928), execution(in-use=204248, alloc=207856)

JPPD:  Considering Cost-based predicate pushdown from query block SEL$5DA710D3 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$5DA710D3 (#1)
kkqctdrvTD-start: :
    call(in-use=3520, alloc=16344), compile(in-use=142128, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=3520, alloc=16344), compile(in-use=142736, alloc=173928), execution(in-use=204248, alloc=207856)

kkqctdrvTD-end:
    call(in-use=3520, alloc=16344), compile(in-use=143056, alloc=173928), execution(in-use=204248, alloc=207856)

JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$5DA710D3 (#1)
FPD: Considering simple filter push in query block SEL$5DA710D3 (#1)
"T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL
try to generate transitive predicate from check constraints for query block SEL$5DA710D3 (#1)
finally: "T2"."ID"="T1"."ID" AND "T2"."PAD" IS NOT NULL

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"."PAD" IS NOT NULL
kkoqbc: optimizing query block SEL$5DA710D3 (#1)

        :
    call(in-use=3808, alloc=16344), compile(in-use=144360, alloc=173928), execution(in-use=204248, alloc=207856)

kkoqbc-subheap (create addr=0x7f1f25745618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t1.id AND t2.pad IS NOT NULL)


SELECT "T1"."ID" "ID",
       "T1"."N" "N",
       "T1"."PAD" "PAD"
  FROM "CHRIS"."T2" "T2",
       "CHRIS"."T1" "T1"
 WHERE "T2"."ID"="T1"."ID"
   AND "T2"."PAD" IS NOT NULL