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

쿼리 변환 - 윈도우 함수를 사용한 서브쿼리 제거 (휴리스틱 기반)

***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$35430991 (#1)
JPPD:   Checking validity of push-down from query block SEL$35430991 (#1) to query block SEL$4F4DF0AE (#2)
Check Basic Validity for Non-Union View for query block SEL$4F4DF0AE (#2)
JPPD:     JPPD bypassed: No valid join condition found.
JPPD:   No valid views found to push predicate into.
kkqctdrvTD-cleanup: transform(in-use=7072, alloc=8136) :
    call(in-use=5768, alloc=16344), compile(in-use=190008, alloc=218312), execution(in-use=256232, alloc=256736)

kkqctdrvTD-end:
    call(in-use=5768, alloc=16344), compile(in-use=182608, alloc=218312), execution(in-use=256232, alloc=256736)

JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$35430991 (#1)
JPPD:   No valid views found to push predicate into.
query block SEL$1 transformed to SEL$35430991 (#1)
FPD: Considering simple filter push in query block SEL$35430991 (#1)
"VW_WIF_1"."VW_COL_5" IS NOT NULL
try to generate transitive predicate from check constraints for query block SEL$35430991 (#1)
finally: "VW_WIF_1"."VW_COL_5" IS NOT NULL

FPD: Considering simple filter push in query block SEL$4F4DF0AE (#2)
"T1"."ID"="T2"."T1_ID"
try to generate transitive predicate from check constraints for query block SEL$4F4DF0AE (#2)
finally: "T1"."ID"="T2"."T1_ID"

OJE: Begin: find best directive for query block SEL$4F4DF0AE (#2)
OJE: End: finding best directive for query block SEL$4F4DF0AE (#2)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_WIF_1"."ITEM_1" "ID","VW_WIF_1"."ITEM_2" "N","VW_WIF_1"."ITEM_3" "ID","VW_WIF_1"."ITEM_4" "N" FROM  (SELECT "T1"."ID" "ITEM_1","T1"."N" "ITEM_2","T2"."ID" "ITEM_3","T2"."N" "ITEM_4",CASE "T2"."N" WHEN MAX("T2"."N") OVER ( PARTITION BY "T2"."T1_ID") THEN "T2".ROWID END  "VW_COL_5" FROM "CHRIS"."T2" "T2","CHRIS"."T1" "T1" WHERE "T1"."ID"="T2"."T1_ID") "VW_WIF_1" WHERE "VW_WIF_1"."VW_COL_5" IS NOT NULL
kkoqbc: optimizing query block SEL$4F4DF0AE (#2)

        :
    call(in-use=6056, alloc=32712), compile(in-use=185008, alloc=218312), execution(in-use=256392, alloc=256736)

kkoqbc-subheap (create addr=0x7f1deadf5618)

(중간 생략)

kkoqbc-subheap (create addr=0x7f1deadf5618)
****************
QUERY BLOCK TEXT
****************
SELECT t1.id, t1.n, t2.id, t2.n
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t2.n = (SELECT max(n)
            FROM t2
            WHERE t2.t1_id = t1.id)


SELECT "VW_WIF_1"."ITEM_1" "ID",
       "VW_WIF_1"."ITEM_2" "N",
       "VW_WIF_1"."ITEM_3" "ID",
       "VW_WIF_1"."ITEM_4" "N"
  FROM (SELECT "T1"."ID" "ITEM_1",
               "T1"."N" "ITEM_2",
               "T2"."ID" "ITEM_3",
               "T2"."N" "ITEM_4",
               CASE "T2"."N" WHEN MAX("T2"."N") OVER ( PARTITION BY "T2"."T1_ID") THEN "T2".ROWID
               END "VW_COL_5"
          FROM "CHRIS"."T2" "T2",
               "CHRIS"."T1" "T1"
         WHERE "T1"."ID"="T2"."T1_ID") "VW_WIF_1"
 WHERE "VW_WIF_1"."VW_COL_5" IS NOT NULL