꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 변환 - FULL OUTER 조인 변환 (11.1버전 전까지)


************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$5 (#1)
kkqctdrvTD-start: :
    call(in-use=18368, alloc=32712), compile(in-use=189960, alloc=222328), execution(in-use=281248, alloc=285128)

Check Basic Validity for Non-Union View for query block SET$1 (#2)
JPPD:     JPPD bypassed: View is a set query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
    call(in-use=18368, alloc=32712), compile(in-use=191888, alloc=222328), execution(in-use=281248, alloc=285128)

kkqctdrvTD-end:
    call(in-use=18368, alloc=32712), compile(in-use=193536, alloc=222328), execution(in-use=281248, alloc=285128)

JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$5 (#1)
JPPD:   No valid views found to push predicate into.
query block SEL$5 (#1) unchanged
FPD: Considering simple filter push in query block SEL$5 (#1)
 ??
FPD: Considering simple filter push in query block SEL$4CF44C19 (#3)
"T2"."T1_ID" IS NULL
try to generate transitive predicate from check constraints for query block SEL$4CF44C19 (#3)
finally: "T2"."T1_ID" IS NULL

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

OJE: Begin: find best directive for query block SEL$4CF44C19 (#3)
OJE: End: finding best directive for query block SEL$4CF44C19 (#3)
OJE: Begin: find best directive for query block SEL$58A6D7F6 (#4)
OJE: Considering outer-join elimination on query block SEL$58A6D7F6 (#4)
OJE: considering predicate"T1"."ID"="T2"."T1_ID"(+)

rejected
OJE:   outer-join not eliminated
OJE: End: finding best directive for query block SEL$58A6D7F6 (#4)
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */ "from$_subquery$_003"."QCSJ_C000000000300000" "ID","from$_subquery$_003"."QCSJ_C000000000300002" "N","from$_subquery$_003"."QCSJ_C000000000300004" "PAD","from$_subquery$_003"."QCSJ_C000000000300001" "ID","from$_subquery$_003"."T1_ID" "T1_ID","from$_subquery$_003"."T1_ID_NN" "T1_ID_NN","from$_subquery$_003"."QCSJ_C000000000300003" "N","from$_subquery$_003"."QCSJ_C000000000300005" "PAD" FROM  ( (SELECT "T1"."ID" "QCSJ_C000000000300000","T1"."N" "QCSJ_C000000000300002","T1"."PAD" "QCSJ_C000000000300004","T2"."ID" "ID","T2"."T1_ID" "T1_ID","T2"."T1_ID_NN" "T1_ID_NN","T2"."N" "N","T2"."PAD" "PAD" FROM "CHRIS"."T1" "T1","CHRIS"."T2" "T2" WHERE "T1"."ID"="T2"."T1_ID"(+)) UNION ALL  (SELECT NULL,NULL,NULL,"T2"."ID" "ID","T2"."T1_ID" "T1_ID","T2"."T1_ID_NN" "T1_ID_NN","T2"."N" "N","T2"."PAD" "PAD" FROM "CHRIS"."T2" "T2" WHERE "T2"."T1_ID" IS NULL)) "from$_subquery$_003"
kkoqbc: optimizing query block SEL$58A6D7F6 (#4)

(중간 생략)

kkoqbc-subheap (create addr=0x2a9733a658)
****************
QUERY BLOCK TEXT
****************
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id

SELECT /*+ NO_NATIVE_FULL_OUTER_JOIN */
       "from$_subquery$_003"."QCSJ_C000000000300000" "ID",
       "from$_subquery$_003"."QCSJ_C000000000300002" "N",
       "from$_subquery$_003"."QCSJ_C000000000300004" "PAD",
       "from$_subquery$_003"."QCSJ_C000000000300001" "ID",
       "from$_subquery$_003"."T1_ID" "T1_ID",
       "from$_subquery$_003"."T1_ID_NN" "T1_ID_NN",
       "from$_subquery$_003"."QCSJ_C000000000300003" "N",
       "from$_subquery$_003"."QCSJ_C000000000300005" "PAD"
  FROM ( (SELECT "T1"."ID" "QCSJ_C000000000300000",
                 "T1"."N" "QCSJ_C000000000300002",
                 "T1"."PAD" "QCSJ_C000000000300004",
                 "T2"."ID" "ID",
                 "T2"."T1_ID" "T1_ID",
                 "T2"."T1_ID_NN" "T1_ID_NN",
                 "T2"."N" "N",
                 "T2"."PAD" "PAD"
            FROM "CHRIS"."T1" "T1",
                 "CHRIS"."T2" "T2"
           WHERE "T1"."ID"="T2"."T1_ID"(+))
           UNION ALL
           (SELECT NULL,
                   NULL,
                   NULL,
                   "T2"."ID" "ID",
                   "T2"."T1_ID" "T1_ID",
                   "T2"."T1_ID_NN" "T1_ID_NN",
                   "T2"."N" "N",
                   "T2"."PAD" "PAD"
              FROM "CHRIS"."T2" "T2"
             WHERE "T2"."T1_ID" IS NULL)) "from$_subquery$_003"


----- Current SQL Statement for this session (sql_id=95zs2444p67ha) -----
EXPLAIN PLAN FOR
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id
sql_text_length=106
sql=EXPLAIN PLAN FOR
SELECT /*+ no_native_full_outer_join */ *
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id  | Operation             | Name    | Rows  | Bytes | Cost  | Time      |
----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |         |       |       |     7 |           |
| 1   |  VIEW                 |         |     2 |   364 |     7 |  00:00:01 |
| 2   |   UNION-ALL           |         |       |       |       |           |
| 3   |    HASH JOIN OUTER    |         |     1 |   182 |     5 |  00:00:01 |
| 4   |     TABLE ACCESS FULL | T1      |     1 |    78 |     2 |  00:00:01 |
| 5   |     TABLE ACCESS FULL | T2      |     1 |   104 |     2 |  00:00:01 |
| 6   |    TABLE ACCESS FULL  | T2      |     1 |   104 |     2 |  00:00:01 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
3 - access("T1"."ID"="T2"."T1_ID")
6 - filter("T2"."T1_ID" IS NULL)

----- Current SQL Statement for this session (sql_id=a3gagumsws0j9) -----
EXPLAIN PLAN FOR
SELECT *
FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n
sql_text_length=68
sql=EXPLAIN PLAN FOR
SELECT *
FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id  | Operation              | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |         |       |       |     5 |           |
| 1   |  VIEW                  | VW_FOJ_0|     1 |   182 |     5 |  00:00:01 |
| 2   |   HASH JOIN FULL OUTER |         |     1 |   182 |     5 |  00:00:01 |
| 3   |    TABLE ACCESS FULL   | T1      |       |       |     2 |  00:00:01 |
| 4   |    TABLE ACCESS FULL   | T2      |     1 |   104 |     2 |  00:00:01 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - access("T1"."N"="T2"."N")