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

쿼리 변환 - 뷰 병합

단순 뷰 병합 (휴리스틱 기반)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."PAD" "PAD","T3"."ID" "ID","T3"."T1_ID" "T1_ID","T3"."PAD" "PAD" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2","CHRIS"."T3" "T3" WHERE "T1"."ID"="T3"."T1_ID" AND "T3"."ID">6 AND "T1"."ID"="T2"."T1_ID"
kkoqbc: optimizing query block SEL$5428C7F1 (#0)

        :
    call(in-use=5600, alloc=16344), compile(in-use=78312, alloc=80536), execution(in-use=120800, alloc=121896)

kkoqbc-subheap (create addr=0x7fc486325618)
****************
QUERY BLOCK TEXT
****************
SELECT *
FROM (SELECT t1.* FROM t1, t2 WHERE t1.id = t2.t1_id) t12,
     (SELECT * FROM t3 WHERE id > 6) t3
WHERE t12.id = t3.t1_id

SELECT "T1"."ID" "ID",
       "T1"."PAD" "PAD",
       "T3"."ID" "ID",
       "T3"."T1_ID" "T1_ID",
       "T3"."PAD" "PAD"
  FROM "CHRIS"."T1" "T1",
       "CHRIS"."T2" "T2",
       "CHRIS"."T3" "T3"
 WHERE "T1"."ID"="T3"."T1_ID"
   AND "T3"."ID">6
   AND "T1"."ID"="T2"."T1_ID"

복잡한 뷰 병합 (비용 기반)

********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD:  Current where clause predicates "T1"."N"="T2"."N"

try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T1"."N"="T2"."N"

FPD: Considering simple filter push (pre rewrite) in query block SEL$2 (#0)
FPD:  Current where clause predicates  ??

OBYE:   Considering Order-by Elimination from view SEL$1 (#0)

(중간 생략)
****************************************
 Cost-Based Group-By/Distinct Placement
****************************************
GBP/DP: Checking validity of GBP/DP for query block SEL$F5BB74E1 (#1)
GBP: Checking validity of group-by placement for query block SEL$F5BB74E1 (#1)
GBP: Bypassed: complex view merging.
DP: Checking validity of distinct placement for query block SEL$F5BB74E1 (#1)
DP: Bypassed: Query has invalid constructs.
kkqctdrvTD-cleanup: transform(in-use=4528, alloc=8136) :
    call(in-use=5768, alloc=16344), compile(in-use=162904, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-end:
    call(in-use=5768, alloc=16344), compile(in-use=155344, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
    call(in-use=5768, alloc=16344), compile(in-use=155344, alloc=174136), execution(in-use=207560, alloc=208064)

TE: Checking validity of table expansion for query block SEL$F5BB74E1 (#1)
TE: Bypassed: No partitioned table in query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=5768, alloc=16344), compile(in-use=156312, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-end:
    call(in-use=5768, alloc=16344), compile(in-use=156992, alloc=174136), execution(in-use=207560, alloc=208064)

TE: Checking validity of table expansion for query block SEL$F5BB74E1 (#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",SUM("T2"."N") "SUM_N" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."N"="T2"."N" GROUP BY "T2"."N","T1".ROWID,"T1"."PAD","T1"."N","T1"."ID"
ST: not valid since star transformation parameter is FALSE
kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
    call(in-use=5816, alloc=16344), compile(in-use=156992, alloc=174136), execution(in-use=207560, alloc=208064)

JF: Checking validity of join factorization for query block SEL$F5BB74E1 (#1)
JF: Bypassed: not a UNION or UNION-ALL query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=5816, alloc=16344), compile(in-use=157960, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-end:
    call(in-use=5816, alloc=16344), compile(in-use=158640, alloc=174136), execution(in-use=207560, alloc=208064)

JPPD:  Considering Cost-based predicate pushdown from query block SEL$F5BB74E1 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$F5BB74E1 (#1)
kkqctdrvTD-start: :
    call(in-use=5816, alloc=16344), compile(in-use=158640, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=5816, alloc=16344), compile(in-use=159608, alloc=174136), execution(in-use=207560, alloc=208064)

kkqctdrvTD-end:
    call(in-use=5816, alloc=16344), compile(in-use=160288, alloc=174136), execution(in-use=207560, alloc=208064)

JPPD: Applying transformation directives
query block SEL$1 transformed to SEL$F5BB74E1 (#1)
FPD: Considering simple filter push in query block SEL$F5BB74E1 (#1)
"T1"."N"="T2"."N"
try to generate transitive predicate from check constraints for query block SEL$F5BB74E1 (#1)
finally: "T1"."N"="T2"."N"

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."N" "N","T1"."PAD" "PAD",SUM("T2"."N") "SUM_N" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."N"="T2"."N" GROUP BY "T2"."N","T1".ROWID,"T1"."PAD","T1"."N","T1"."ID"
kkoqbc: optimizing query block SEL$F5BB74E1 (#1)

        :
    call(in-use=6056, alloc=16344), compile(in-use=161592, alloc=174136), execution(in-use=207560, alloc=208064)

kkoqbc-subheap (create addr=0x7f9f10765560)
****************
QUERY BLOCK TEXT
****************
SELECT t1.*, t2.sum_n
FROM t1, (SELECT /*+ merge */ n, sum(n) AS sum_n
          FROM t2
          GROUP BY n) t2
WHERE t1.n = t2.n


SELECT "T1"."ID" "ID",
       "T1"."N" "N",
       "T1"."PAD" "PAD",
       SUM("T2"."N") "SUM_N"
  FROM "CHRIS"."T1" "T1",
       "CHRIS"."T2" "T2"
 WHERE "T1"."N"="T2"."N"
 GROUP BY "T2"."N",
       "T1".ROWID,
       "T1"."PAD",
       "T1"."N",
       "T1"."ID"