1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 2.12 FOJC(Full Outer Join Conversion) Full Outer 조인을 Union All로 변경하라

2.12 FOJC(Full Outer Join Conversion) Full Outer 조인을 Union All로 변경하라 (by hun1361) [2013.06.11]

2.12 FOJC* (Full Outer Join Conversion) : Full Outer 조인을 Union All로 변경하라

  • Full Outer Join의 일반적인 개념
    하나의 테이블 기준으로 Outer Join을 한다.
    Union
    반대편 테이블을 기준으로 다시 Outer Join을 한다.
    (Union이 Union All로 바뀜)
  • _optimizer_native_full_outer_join
    full outer join 발생시 이 파라미터가 off 이면(10g) 문장은 두개의 쿼리블럭(left outer join 과 anti join)으로 나뉘어져 union all 되게 변환된다.
    이 파라미터를 force 로 두면(11g) anti join 부분이 제거되어 성능이득이 있다.
    이 기능을 Native Full Outer Join 이라 하며 2.13 장에 소개된다
{code:SQLtitle= _optimizer_native_full_outer_join = OFF;borderStyle=solid}
alter session set "_optimizer_native_full_outer_join" = OFF;

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

SELECT a.employee_id, a.first_name, a.last_name, a.email, b.department_name
FROM employee a FULL OUTER JOIN department b
ON (a.department_id = b.department_id) ;

ALTER SESSION SET EVENTS '10053 trace name context off';

---+
---+

IdOperationNameRowsBytesCostTime

---+
---+

0SELECT STATEMENT7
1VIEW1248680700:00:01
2UNION-ALL
3HASH JOIN OUTER1074708500:00:01
4TABLE ACCESS FULLEMPLOYEE1073210200:00:01
5TABLE ACCESS FULLDEPARTMENT27378200:00:01
6MERGE JOIN ANTI17289100:00:01
7TABLE ACCESS BY INDEX ROWIDDEPARTMENT273780
8INDEX FULL SCANDEPT_ID_PK270
9SORT UNIQUE107321100:00:01
10INDEX FULL SCANEMP_DEPARTMENT_IX1073210

---+
---+
Predicate Information:
--
3 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
9 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
9 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")


* 윗부분은 분명히 Outer 조인으로 풀렸지만 아랫부분은 Anti 조인으로 풀렸으므로 이것은 서브쿼리를 사용한 것이다
즉,옵티마이져는 Full Outer Join을 아래처럼 Union All로 재작성한 것이다
|{code:SQL|title= SQL변환 |borderStyle=solid}
explain plan for
SELECT *
  FROM (SELECT a.employee_id, a.first_name, a.last_name, a.email, b.department_name
          FROM employee a, department b
         WHERE a.department_id = b.department_id(+)
        UNION ALL
        SELECT NULL, NULL, NULL, NULL, b.department_name
          FROM department b
         WHERE NOT EXISTS (SELECT 1
                             FROM employee a
                            WHERE a.department_id = b.department_id)
       );

select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |   124 |  8680 |     7  (43)| 00:00:01 |
|   1 |  VIEW                          |                   |   124 |  8680 |     7  (43)| 00:00:01 |
|   2 |   UNION-ALL                    |                   |       |       |            |          |
|*  3 |    HASH JOIN OUTER             |                   |   107 |  4708 |     5  (20)| 00:00:01 |
|   4 |     TABLE ACCESS FULL          | EMPLOYEE          |   107 |  3210 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL          | DEPARTMENT        |    27 |   378 |     2   (0)| 00:00:01 |
|   6 |    MERGE JOIN ANTI             |                   |    17 |   289 |     1 (100)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENT        |    27 |   378 |     0   (0)| 00:00:01 |
|   8 |      INDEX FULL SCAN           | DEPT_ID_PK        |    27 |       |     0   (0)| 00:00:01 |
|*  9 |     SORT UNIQUE                |                   |   107 |   321 |     1 (100)| 00:00:01 |
|  10 |      INDEX FULL SCAN           | EMP_DEPARTMENT_IX |   107 |   321 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID"(+))
9 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

24 rows selected.
  • 10053 Trace 파일에서 Full Outer Join과 같은 Ouery Transformation을 분석할 때 도움이 되는 팁
    Trace의 가장 마지막에 Ouery Block Registry 정보가 있는데 이것이 Ouery Transformation의 요약정보이다.
    {code:SQL
title= Query Block RegistryborderStyle=solid}
Query Block Registry:
SEL$5 0x10b08af0 (PARSER) FINAL
SET$1 0x10b00048 (PARSER) FINAL
SEL$4 0x10b00318 (PARSER)
SEL$45B11F62 0x10b00318 (SUBQUERY UNNEST SEL$4; SEL$3) FINAL
SEL$3 0x10afcf08 (PARSER)
SEL$45B11F62 0x10b00318 (SUBQUERY UNNEST SEL$4; SEL$3) FINAL
SEL$2 0x10affd78 (PARSER)
SEL$58A6D7F6 0x10affd78 (VIEW MERGE SEL$2; SEL$1) FINAL
SEL$1 0x10afe938 (PARSER)
SEL$58A6D7F6 0x10affd78 (VIEW MERGE SEL$2; SEL$1) FINAL
{code}
  • Ouery Block Registry을 분석할 때는 아래서부터 위로 분석하여야 한다
    1.쿼리블럭 SEL$1 이 SEL$2에 MERGE 되어 쿼리블럭 SEL$58A6D7F6가 만들어 졌다. 이것은 인라인뷰 등이 해체될 때 VIEW MERGE가 발생된다.
    2. 쿼리블럭 SEL$3 이 SEL$4로 Unnesting 되어 쿼리블럭 SEL$45B11F62가 만들어 졌다 이것은 Anti 조인을 의미한다.
    3. 1번과 2번을 Union All로 연결시켜서 쿼리블럭 SET$1 이 만들어 졌다 Union이나 Minus 등의 Set연산자가 사용되면 쿼리블럭명이 SET$N으로 만들어진다
    4. 최종 쿼리블럭 SEL$5를 만든다.
{code:SQLtitle= UNPARSED OUERYborderStyle=solid}
SELECT "A"."EMPLOYEE_ID" "EMPLOYEE_ID",
"A"."FIRST_NAME" "FIRST_NAME",
"A"."LAST_NAME" "LAST_NAME",
"A"."EMAIL" "EMAIL",
"A"."DEPARTMENT_ID" "QCSJ_C000000000300000",
"from$_subquery$_004"."DEPARTMENT_ID_1" "QCSJ_C000000000300001",
"from$_subquery$_004"."DEPARTMENT_NAME_0" "DEPARTMENT_NAME"
FROM "TLO"."EMPLOYEE" "A",
LATERAL( (SELECT "B"."DEPARTMENT_NAME" "DEPARTMENT_NAME_0",
"B"."DEPARTMENT_ID" "DEPARTMENT_ID_1"
FROM "TLO"."DEPARTMENT" "B"
WHERE "A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
)(+) "from$_subquery$_004"
{code}
  • UNPARSED QUERY는 Transformer가 SQL 변환작업시에 생성되는 TEMP성 SQL, 분석시 요긴하게 사용됨
  • 오라클은 Outer 조인을 변환할 시에는 많은 경우에 Outer쪽을 View로 미리 변환 시켜버린다
    View Merging과 JPPD(Join Predicate Push Down)를 동시에 Costing 해야하기때문
    이것은 매우 어려운 개념이기 때문에 지금은 이린 것이 있다는 정도만 알아 두자. 이 관점은 4.12 장과 4.13 장에서 자세히 소개된다.
    JPPD 또한 3.9 장부터 3.13 장까지 자세히 설명된다.
  • 쿼리블럭 SEL$1 ~ SEL$4에 해당하는 테이블정보
    {code:SQL
title= QUERY BLOCK SIGNATUREborderStyle=solid}
Registered qb: SEL$1 0x10afe938 (PARSER)
-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=459677 hint_alias="B"@"SEL$1"

Registered qb: SEL$2 0x10affd78 (PARSER)

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$2 nbfros=2 flg=0
fro(0): flg=4 objn=459680 hint_alias="A"@"SEL$2"
fro(1): flg=5 objn=0 hint_alias="from$_subquery$_004"@"SEL$2"

Registered qb: SEL$3 0x10afcf08 (PARSER)

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$3 nbfros=1 flg=0
fro(0): flg=4 objn=459680 hint_alias="A"@"SEL$3"

Registered qb: SEL$4 0x10b00318 (PARSER)

-
QUERY BLOCK SIGNATURE
-
signature (): qb_name=SEL$4 nbfros=1 flg=0
fro(0): flg=4 objn=459677 hint_alias="B"@"SEL$4"


|* Query Parser가 SQL을 수집하여 개별 QUERY BLOCK으로 구분한 것이다.
Query Transformation이 발생하면 QUERY VLOCK의 명이 바뀔수 있으므로 힌트에 쿼리블록명을 사용할 경우 항상 확인 후 사용해야 함
{code:SQL|title= 쿼리블록명 확인 |borderStyle=solid}
SELECT * FROM TABLE(DBMS_XPlan.dispay_cursor(NULL,NULL,'allstats last -rows +alias +outline +predicate')); -- alias추가

|

  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 2.12 FOJC(Full Outer Join Conversion) Full Outer 조인을 Union All로 변경하라