'Hash Join Right Anti'에 해당되는 글 1건

  1. 2009.03.02 Hash Join Right (Semi/Anti/Outer) 의 용도 (17)
<2009.03.11 : 아래 내용중의 Right 의 의미는 Oracle 10g Performance Tuning Guide 19-21 에 나온대로 "올바른" 이란 뜻이 아니라 "Left 혹은 Right Outer Join 시의 Right" 의 의미로 바꿉니다.
하지만 이글의 핵심인 "이전버젼까지는 항상 후행집합으로 되던것이 10g 부터는 선행집합이 될수 있다." 는 바뀐것이 없습니다. >

 
오늘은 Hash Join Right  (Semi/Anti/Outer) 의 용도에 대하여 알아보려한다.
Oracle 10g 부터 Hash Join 은 서서히 변화하기 시작하였다.
특히 Hash Join Right  (Semi/Anti/Outer) 기능을 사용하여 대용량 집합의 Join 시 획기적인 성능향상을 이루었다.
Hash Join 에서 Right 옵션이 붙음으로서 획기적인 성능향상을 이루었다는 이유는 무엇일까?
Semi/Anti Join은 항상 메인 쿼리가 수행된 후 서브쿼리의 데이터를 체크하는 방식이다.
따라서 Semi/Anti Join 의 경우 서브쿼리는 항상 후행집합이 될수 밖에 없다.
Hash Outer Join 의 경우도 마찬가지로 (+) 표시가 붙는 쪽의 집합은 항상 후행집합이 될수 밖에 없었다.

하지만 10g 부터 Hash Join Right (Semi/Anti/Outer) 기능이 나오게 되면서 서브쿼리 혹은 아우터 Join 되는 쪽의 집합이 선행집합이 될수 있다.
이때 Right 의 뜻은 left 집합 대신에 right(후행집합)을 선행집합으로 하겠다는 뜻이다.
9i 까지 Hash Join (Semi/Anti/Outer)의 경우에 눈물을 머금고 대량의 집합을 선행처리할수 밖에 없었지만 이제는 자유롭게 선행집합을 선택할수 있는것이다.
국내외 튜닝책을 막론하고 이막강한 기능에 대하여 제대로 다루는 것을 본적이 없다.
왜냐하면 초대용량 DB(VLDB)에서 Join Method 를 튜닝해본 사람만이 이 기능이 얼마나 중요한지 피부로 느낄수 있기 때문이다.
아래의 스크립트를 보자.
환경 : 10.2.0.4

1.테이블을 생성하고 Aanlyze 를 한다.

1.테이블 생성
CREATE TABLE BIG_EMP AS
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO
  FROM EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE BIG_EMP
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

dbms_stats.gather_table_stats(user, 'BIG_EMP', cascade => true); 


2.Hash Semi Join 을 유도한다.

select a.empno, a.sal
  from   big_emp a
  where  exists (select /*+ use_hash(b) */
                                  b.deptno
                          from  dept b
                       where  b.deptno = a.deptno
                      ) ;

--------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |    4  (25)|  26000 |00:00:00.06 |     176 813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.01 |     175 |          |
--------------------------------------------------------------------------------------------------------



위의 통계정보를 보면 176 블럭을  scan 했으며  Hash area size 를 813 K를 사용했다는걸 알수 있다.
작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택하고 BIG_EMP 테이블을
후행(Probe) 테이블로 Hash 조인 함으로서 최적의 조인이 되었다.
그렇다면 Hash Join Right Semi 를 사용하지 않으면 어떻게 될것인가?
Subquery Unnesting 기능을 이용하면 작은 용량의 테이블인 DEPT 를 Driving 집합(Build Input) 으로 선택할수는 있다.
하지만 아래처럼 약간의 손해를 감수해야 한다.

select /*+ gather_plan_statistics ordered */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ use_hash(b) */
                                b.deptno
                         from  dept b
                      where  b.deptno = a.deptno
                      );

위의 SQL 을 보면 강제로 MAIN 쿼리에 ordered 힌트를 주어 Semi Join 이 아닌 SubQuery Unnesting 이 되었다.
ordered 힌트를 사용한 이유는 서브쿼리가 Semi Join 에 실패할 경우  Subquery Unnesting 을 시도하게 되는데
이때 서브쿼리블럭이 From 절의 가장 좌측으로 오기 때문이다.
사용자가 ordered 힌트등을 사용하면 오라클 내부적인 leading 힌트와 Swap_join_inputs 힌트 등이 Override 되어 무시된다.
따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다.
이제 Plan 을 보자.

------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts |Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |         |      1 |    5  (40)|  26000 |00:00:00.37 |     176808K (0)|
|   2 |   SORT UNIQUE      |         |      1 |    1   (0)|      4 |00:00:00.01 |       1 | 2048  (0)|
|   3 |    INDEX FULL SCAN | PK_DEPT |      1 |    1   (0)|      4 |00:00:00.01 |       1 |          |
|*  4 |   TABLE ACCESS FULL| BIG_EMP |      1 |    2   (0)|  26000 |00:00:00.31 |     175 |          |
------------------------------------------------------------------------------------------------------

 
처음 예제와 조인순서와 Scan 한 블럭수및 Hash area size 사용량은 대동소이 하지만 Subquery Unnesting 이 발생하여 불필요한 Sort 가 발생 하였다.
위의 SQL 의 경우 Subquery Unnesting 은 메인쿼리의 결과집합을 보존하기 위하여 Sort Unique 혹은 Hash Unique 작업이 추가적으로 발생된다.
Subquery Unnesting 이 항상 나쁜것은 아니지만 대용량 집합간의 조인시는 엄청난 부담이 될수 밖에 없다.
서브쿼리쪽은 Sort Unique 작업이 추가적으로 필요하기 때문이다.
그렇다고 덩치가 큰 BIG_EMP를 선행테이블로 할수도 없는것이다.
이것이 바로 Hash Join Right Semi 가 10g 에서 나타난 이유이다.
그렇다면 이럴 경우에 강제로 Hash Join Right Semi 를 발생시키려면 어떻게 해야 되겠는가?
이럴때 간단하게 사용할수 있는것이 QB_NAME 을 이용한 Global 힌트와 USE_HASH 및 SWAP_JOIN_INPUT 힌트이다.
아래의 스크립트를 보자.

select /*+ gather_plan_statistics LEADING(A) USE_HASH(@sub B) SWAP_JOIN_INPUTS(@sub B) */ a.empno, a.sal
 from   big_emp a
 where  exists (select /*+ qb_name(sub) */
                                b.deptno
                        from  dept b
                      where  b.deptno = a.deptno
                      );

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Starts | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |     4  (25)|  26000 |00:00:01.05 |     176 |  813K (0)|
|   2 |   INDEX FULL SCAN    | PK_DEPT |      1 |     1   (0)|      4 |00:00:00.01 |       1 |          |
|*  3 |   TABLE ACCESS FULL  | BIG_EMP |      1 |     2   (0)|  26000 |00:00:00.99 |     175 |          |
---------------------------------------------------------------------------------------------------------


다시 정상적인 Hash Join Right Semi 로 돌아왔다.
간단히 힌트를 설명하자면 QB_NAME 은 쿼리블럭명을 강제로 지정하는 힌트이고 Swap_join_inputs 힌트는
Probe 쪽 집합(후행 집합) 을 강제로 Build Input 집합(선행집합) 으로 바꾸는 힌트이다.
그리고 Use_hash 힌트에 대하여 한마디 하자면 원래 Use_hash 힌트는 후행 집합에 대해서만 사용하는 힌트이다.
하지만 USE_HASH(A B) 이런식으로 사용해도 ORACLE 이 힌트를 아래처럼 변환시켜버린다.
USE_HASH(A B) --> LEADING(A B) USE_HASH(B)
오라클사에서 명시적인 용어가 없기 때문에 필자는 이것을  Internal Hint Transformation 이라 부른다.
다음에 기회가 되면 Internal Hint Transformation 에 대하여 글을 올릴까 한다.

결론 : 10g 부터 나온 Hash Join Right (Semi/Anti/Outer) 기능을 적재적소에 활용하면 대용량 집합간의 join 성능을 획기적으로 향상시킬수 있다.

참고로 Hash Join Right Anti Plan 으로 유도하는 것은 Exists 대신 Not Exists 로 바꾸면 된다.
Hash Join Right Outer 를 유도하는 예제는 아래와 같다.

select /*+ LEADING(A) USE_HASH(B) SWAP_JOIN_INPUTS(B) */ a.empno, a.sal
 from   big_emp a,
          dept b
where a.deptno = b.deptno(+)  
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.03.03 11:09 신고  댓글주소  수정/삭제  댓글쓰기

    저는 Right의 의미를 Outer Join처럼 Join Operation의 왼쪽(Left) Input으로만 위치할 수 있었던 것을 오른쪽(Right)에 둔다는 의미로 받아 들였는데요. 그러니까..

    select ... from t1, t2 where t1.c1 = t2.c1(+)

    이렇게 되면, Outer Join의 한계상 반드시 Join 순서가 (t1 --> t2)가 되었어야 했는데(t1이 왼쪽에 올 수 밖에 없음), 10g부터는 이 순서를 (t2 --> t1) 으로(t1이 오른쪽으로) 해주겠다는 의미로 생각했습니다. 아마 이것을 구현하기는 쉽지 않았을거 같습니다. Oracle 개발자들의 노고를 치하해야겠죠.

    10g가 Outer, Semi, Anit에 대해서 많은 경우 Right Join을 자연스럽게 해주니까 이것의 혜택을 전혀 인식도 못하고 쓰는 경우가 많죠. 이 시리즈를 잘 정리해서 블로그에 올리시면 상당히 좋은 자료들이 될 거 같습니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.03.03 12:45 신고  댓글주소  수정/삭제

      Right 의 의미를 Outer 조인의 경우 그렇게 생각할수 있을거 같습니다.
      물론 이기능을 구현한 설계자에게 Right 의 의미를 정확히 물어보는것이 가장 확실히 알수 있는 방법이 되겠습니다.
      그리고 Hash Right Join 기능을 구현한것은 어렵기는해도 가능한데 Nested Loop Right Join 은 개념상 로직 구현이 불가능 할것으로 생각합니다.
      좋은 정보 감사드립니다.

  2. 고구마 2009.03.03 14:48  댓글주소  수정/삭제  댓글쓰기

    열심히 보고 또 보고 있네요.
    감사합니다.

  3. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.03.06 09:35  댓글주소  수정/삭제  댓글쓰기

    오동규님 덕분에 'hash join right'와 'SWAP_JOIN_INPUTS'에 대해 알게되서 도움이 많이 될것 같습니다.

    좋은 내용 감사합니다.

  4. 김시연 2009.03.18 11:16  댓글주소  수정/삭제  댓글쓰기

    오랜만에 들어왔더니 좋은 글들이 많네요. 좋은 글 감사합니다~!

  5. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.03.18 15:22 신고  댓글주소  수정/삭제  댓글쓰기

    오랜만 입니다.
    요즘 바쁘셨나 보네요^^.

  6. 브라이언홍 2009.07.22 17:44  댓글주소  수정/삭제  댓글쓰기

    지난 6월 17일에 진행된 DB2 9.7(KDUG) ‘DB 개발자/관리자의 생산성 향상’에 대한 발표 동영상을 보니 DB2 9.7에서는 Nested Loop Right Join이 가능한가 봅니다. 음...CD를 받으면 설치하고 테스트해 봐야 겠네여..생각나서 처음으로 댓글 달아봅니다..매번 좋은 글을 올려 주셔서 감사해요^^

    http://www.kdug.kr/blog/146
    [Session 3] DB2 9.7과 오라클의 쿼리 최적화 효율성 비교 동영상..

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.22 18:26 신고  댓글주소  수정/삭제

      그렇군요.
      Nested Loop Right Join 은 오라클에서도 됩니다.
      아마도 Nested Loop Right Outer Join 을 이야기 하시는것 같습니다.
      좋은 정보 감사합니다.

  7. 예맥 2010.04.22 12:06  댓글주소  수정/삭제  댓글쓰기

    use_hash(a b)가 leading(a b) + use_hash(b)로 변형된다고 하셨는데 ordered와 leading, use_hash, swap_joins_inputs를 같이 쓰면 헷갈리네요..

    환경: 10.2.0.4

    1. 힌트에 swap_join_inputs을 단독으로 쓰면 힌트가 사용되지 않고
    2. leading과 같이 사용시 swap_join_inputs의 값이 build가 되고
    3. ordered와 사용시 2번과 동일하고
    4. use_hash와 swap_join_inputs사용시 swap_join_inputs의 값이 사용되지 않고

    use_hash는 내부적으로 leading과 use_hash로 변형된다고 하셨는데 정말 변형되는지 궁굼합니다.
    여러개가 같이 쓰여면 헷갈립니다...

    5. leading + use_hash + swap_join_inputs를 사용시 swap_join_inputs가 사용됩니다.


    =============================================================================================================================
    select /*+ gather_plan_statistics leading(a) */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b) */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;

    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics leading(a b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.04 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(a b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics leading(b a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    -------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics ordered swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics ordered swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics use_hash(a b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(a b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(b a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics use_hash(b a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    select /*+ gather_plan_statistics leading(a b) use_hash(b) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.03 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(a b) use_hash(b) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------


    select /*+ gather_plan_statistics leading(b a) use_hash(a) swap_join_inputs(a)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.03 | 176 | 1888K| 1511K| 2336K (0)|
    | 2 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 172 | | | |
    | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 4 | | | |
    -------------------------------------------------------------------------------------------------------------------

    select /*+ gather_plan_statistics leading(b a) use_hash(a) swap_join_inputs(b)
    opt_param('_optimizer_cost_based_transformation','off') */
    a.empno, a.sal, b.deptno
    from big_emp a, dept b
    where a.deptno = b.deptno;
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------
    |* 1 | HASH JOIN | | 1 | 28122 | 28000 |00:00:00.06 | 2033 | 1517K| 1517K| 814K (0)|
    | 2 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
    | 3 | TABLE ACCESS FULL| BIG_EMP | 1 | 28122 | 28000 |00:00:00.01 | 2032 | | | |
    -------------------------------------------------------------------------------------------------------------------

    =============================================================================================================================

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.04.22 18:32 신고  댓글주소  수정/삭제

      안녕하세요. 예맥님
      Swap_join_inputs 힌트는 후행테이블(Probe)에 사용하셔야 합니다.
      그리고 오라클이 order 힌트나 use_hash(a b)를 내부적으로 어떻게 바꾸는지는 아래처럼 포맷을 설정하시면 됩니다. 옵티마이져가 사용한 힌트를 볼 수 있습니다.

      SELECT * FROM
      TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL, 'allstats last -rows +alias +outline +predicate'));

  8. Stargazer 2010.08.20 10:48  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 오동규님.

    먼저 이렇게 좋은 내용들을 공개해주신것에 대해 많은 고마움을 느끼고 있습니다.


    위에서 말씀하시 HASH JOIN RIGHT SEMI에 대해 제가 약간 혼란 스러운것이 있어
    질문을 드립니다.



    본문내용중에..
    "따라서 Semi Join 이 아닌 Subquery Unnesting 되는 것이다."

    subquery unnest와 Semi Join이 별개 operation 라는 느낌을 받았습니다.

    일단 subquery unnest가 되어 subquery 가 1집합을 보장하지 못할때
    subquery 가 먼저 풀리면 sort unique, hahs unique작업 을 하고

    main query가 먼저 풀리면 semi join으로 풀리는 것으로 알고 있는데...


    HASH JOIN RIGHT SEMI 는 일단 먼저 subquery unnest가 발생하는것이 아닌가요??


    전제조건이 subquery unnest가 발생하고
    subquery가 먼저 풀리면 위에서 말씀하신 HASH JOIN RIGHT SEMI 작업이나 또는 sort unique 작업을 하는게 아닌지요..


    답변 부탁드립니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.08.20 11:28 신고  댓글주소  수정/삭제

      Stargazer님 반갑습니다.
      먼저 아래의 글을 읽어보시기 바랍니다.
      http://scidb.tistory.com/entry/SubQuery-Using-Method-1

      글에서 보시는대로 서브쿼리는 크게 5가지로 나눌 수 있습니다.
      _unnest_subquery 파라미터는 semi join에 영향을 끼치지 않고 서브쿼리 Unnesting 에만 영향을 끼칩니다. 반대로 _always_semi_join은 semi join에만 영행을 끼칩니다.

      물론 Unnesting과 Semi/Anti 를 합쳐서 SubQuery Flattening이라고 합니다. 사람들이 SubQuery Flattening을 두리뭉실하게 Unnesting이라고 부릅니다. 하지만 관장하는 파리미터도 다르고 로직도 다릅니다.

      Unnesting과 Semi의 차이는 간단합니다.
      Semi는 첫번째 조건을 만족하면 빠져나가는 것이고 Unnesting은 첫번재 건 뿐만아니라 전체건을 조인해야 하는 것입니다.
      감사합니다.

  9. Stargazer 2010.08.20 19:39  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다.

    말씀하신것처럼 ... Semi와 unnesting은 별개 단계의 작업이란것은 알고 있습니다.

    위에서 말씀하신 Hash Join Right Semi는 sub query부분이 먼저 driving이 되었는데도 불구하고
    1집합을 만드는 작업없이 바로 hash semi right 조인으로 풀렸습니다.


    제가 알고 있는 sub query는 크게 unnest로 발생하는경우와 no unnest가 발생하지 않는 단지 두 가지 경우로
    풀리는걸로 알고 있습니다.

    일단 hash semi right 조인도 sub query가 unnest가 되어 inline view로 변경이되어야 한다는 전제 조건을 만족하는게 아닌가 입니다.

    아님 10g부터는 sub query가 1.unnest로 풀릴경우 2.no_unnest로 풀리경우 3. Hash Join Right Semi로 풀리경우
    이렇게 3가지로 봐야 하는지요?

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.08.24 18:12 신고  댓글주소  수정/삭제

      답변이 늦어 죄송합니다.
      차이점만 알고 계신다면 서브쿼리가 없어지고 From 절로 올라가는 작업(Subquery Flattening)을 unnesting으로 불러도 무방합니다.

      서브쿼리와 관련된 쿼리변환은 3가지가 아니라 15가지 입니다. 물론 책(The Logical Optimizer)을 기준으로 했습니다. Part 2에서 13가지, Part 3의 두가지 입니다.

  10. Stargazer 2010.08.25 20:36  댓글주소  수정/삭제  댓글쓰기

    음..그럼..일단 subquery가 from절로 올라가는거군요. 말씀하신 Subquery Flattening.

    "Subquery Flattening" 사실 이 용어 자체가 저한텐 아직 낯설군요..

    답변 감사하고요... 좋은글, 좋은 책에 대해 늘 감사하게 생각하고 있습니다.