'Join Tuning'에 해당되는 글 3건

  1. 2010.02.26 Oracle 조인 방법 25가지 (15)
  2. 2009.05.09 Nested Loop Join 성능향상과 관련된 2가지 원리 (17)
  3. 2009.03.02 Hash Join Right (Semi/Anti/Outer) 의 용도 (17)

3 부작의 마지막 편
첫 번째,
Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된
Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.


Oracle Data Join Method.pdf

Oracle Data Join Method



PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

Posted by extremedb

댓글을 달아 주세요

  1. Kai 2010.02.26 17:45  댓글주소  수정/삭제  댓글쓰기

    열심히 공부하겠습니다. ( '')/☆

  2. starroot 2010.02.26 17:52  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  3. feelie 2010.03.02 13:08  댓글주소  수정/삭제  댓글쓰기

    oracle data access pattern, partition access pattern,
    그리고 oracle join 25가지 까지....
    좋은정보에 감사할 따름입니다..

  4. 이쁜이 2010.03.02 22:04  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  5. daemon 2010.03.05 15:06  댓글주소  수정/삭제  댓글쓰기

    오동규 컨설턴트님의 글을 항상 감사하게 보고 있습니다.
    이렇게 귀중한 정보를 아무 댓가도 없이 공유해주시는 모습 정말 존경스럽습니다.
    컨설턴트님의 책을 손꼽아 기다리는 한사람으로서 항상 올려주시는 자료에 감사의 말씀이라도
    꼭 드리고 싶었습니다.

  6. Ejql 2010.07.26 10:15  댓글주소  수정/삭제  댓글쓰기

    이제.. 오동규님의 책을 읽을 준비가 되었다고 판단해서. 먼저 기초 공부로 티스토리부터 시작하겠습니다.
    감사합니다.

  7. Favicon of http://bluegom.tistory.com BlogIcon 파란곰팅 2010.12.10 19:47  댓글주소  수정/삭제  댓글쓰기

    오옷... 이런 좋은 자료가 감사합니다.^^....

  8. 힘돌이 2011.10.12 11:46  댓글주소  수정/삭제  댓글쓰기

    오동규님 정말 감사합니다.
    여기서 정말 많은것을 배우게 되네요.

  한 선지자에 의하여 9i/10g 그리고 11g 에서 Nested Loop Join 수행시 buffer pinning 효과에 의한 성능향상이 증명된바 있다.
물론 그글은 명불허전 이다.(해당글 링크:http://ukja.tistory.com/166)
   이미 밝혀진 원리에 대해서 증명이나 검증하는것은 더이상 과학이 아니다. 그리고 재미도 없다. 따라서 오늘은
buffer pinning 에 의한 성능개선이 아닌 또다른 성능개선에 대한 2가지 원리 대해서 연구해보자.

  이제부터 버젼별로 변경된 Nested Loop Join 의 실행계획과 그에 따르는 원리에 대하여 알아보려 한다. 모든 예제는 오라클 설치시 자동으로 설치되는 SH Schema의 customers 테이블과 sales 테이블을 이용한다.
먼저 인덱스를 하나 만든다.

drop index sh.sales_cust_ix;
create index sh.sales_cust_ix on sh.sales(cust_id);


 이어서 buffer cache 를 비우고 SQL 을 실행한다.
고객 테이블을 full scan 하고 sales 테이블과 Nested Loop Join 을 수행한다.

alter system flush buffer_cache;

select /*+ gather_plan_statistics */ count(*)
  from (select /*+ no_merge full(c) use_nl(c s) */
               s.cust_id,
               s.time_id,
               c.cust_year_of_birth
         from  sh.customers c,
               sh.sales s
         where c.cust_id  = s.cust_id  
           and c.cust_year_of_birth between 1960 and 1980
           and s.channel_id  between 2 and 3
           and s.prod_id < 18); 
 
select * from
table(dbms_xplan.display_cursor(null,null, 'allstats last -rows +outline -predicate' ));

Oracle 8i Plan --> just normal
------------------------------------------------
| Id  | Operation                              |
---------------------------------------------- |
|   1 |  SORT AGGREGATE                        |
|   2 |   VIEW                                 |
|   3 |     NESTED LOOPS                       |
|   4 |      TABLE ACCESS FULL                 |
|   5        TABLE ACCESS BY GLOBAL INDEX ROWID|
|   6 |        INDEX RANGE SCAN                |
------------------------------------------------

특별 할것 없는 전통적인 Nested Loop Join 이다.
이제 9i 및 10g 의 plan 을 보자.

9i & 10g Plan --> table prefetch
-------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                      |               |      1 |00:00:05.67 |     245K|   1454 |
|   2 |   VIEW                               |               |   8269 |00:00:05.66 |     245K|   1454 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:05.62 |     245K|   1454 |
|   4 |     NESTED LOOPS                     |               |    327K|00:00:02.83 |   41304 |   1454 |
|   5 |      TABLE ACCESS FULL               | CUSTOMERS     |  20010 |00:00:00.12 |    1457 |   1454 |
|   6 |      INDEX RANGE SCAN                | SALES_CUST_IX |    307K|00:00:00.88 |   39847 |      0 |
-------------------------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_PREFETCH(@"SEL$2" "S"@"SEL$2")
      END_OUTLINE_DATA
  */
 

Oracle 9i 에서 table prefetch 기능이 나오다.
  Inner(후행) 테이블의 위치가 Nested Loop Join 위로 올라가 버렸다. 그리고 오라클이 내부적으로
NLJ_PREFETCH 힌트를 사용하였다. 이것은 어떤 의미를 가지고 있을까?
이러한 현상에 대한 원리는 single block I/O request 에 의한 physical read 시 block 을 prefetch(미리 읽는 작업) 한다는데 있다.
여기서 physical read 란 buffer cache 에 데이터가 없어서 disk 에서 데이터를 read 하는것을 의미한다.
어차피 scan할 data 이므로 미리 엑세스할 물리적 주소를 여러개(운반단위) 모은다음 한번에 read 햐여 buffer cache 에 올리게 되는것이다. 여기서 주의할점은 multi block I/O 를 하는것이 아니라 single block I/O 여러개(운반단위만큼)가 동시에 진행된다는 것이다. 이것을 Vector IO 라고 부른다.(Batch IO 라고도 함)  바로 여기에 성능개선 효과가 있는것이다. 이기능에 의해서 rowid 에 의한 테이블 access 는 8i 에 비해서 상당한 개선 효과가 있는 것이다.(Operation ID 로는 3번이 여기 해당된다.)

이것을 증명하기 위해 v$sesstat 에서 SQL 수행전과 수행후의 value 증가분을 비교해보면 아래와 같다.

NAME                                           DIFF
---------------------------------------- ----------
undo change vector size                        2840
physical read IO requests                      3812
... 중간생략                                      
physical reads cache prefetch                  1344


위에서 보는것과 같이 table prefetch 가 발생하였다. 위의 테스트는 11g 에서 수행된것인데 9i 의 살행계획과 실행통계도 10g 와 대동소이 하다. 11g 에서 이전 버젼(9i/10g) 번젼의 plan 을 나타나게 하려면 NO_NLJ_BATCHING(테이블명) 힌트를 사용하면 된다.  9i 나 10g 에서의 후행 테이블 prefetch에 의한 성능 개선효과는 11g 에 와서야 완벽한 모습을 갖추게 된다.

11g Plan --> Index Vector I/O
------------------------------------------------------------------------------------------------------
|Id  | Operation                             | Name          | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------
|  1 |  SORT AGGREGATE                       |               |      1 |00:00:04.82 |     245K|   1454|
|  2 |   VIEW                                |               |   8269 |00:00:04.81 |     245K|   1454|
|  3 |    NESTED LOOPS                       |               |   8269 |00:00:04.79 |     245K|   1454|
|  4 |     NESTED LOOPS                      |               |    307K|00:00:01.56 |   41304 |   1454|
|  5 |      TABLE ACCESS FULL                | CUSTOMERS     |  20010 |00:00:00.08 |    1457 |   1454|
|  6 |      INDEX RANGE SCAN                 | SALES_CUST_IX |    307K|00:00:00.47 |   39847 |      0|
|  7 |     TABLE ACCESS BY GLOBAL INDEX ROWID| SALES         |   8269 |00:00:01.93 |     203K|      0|
------------------------------------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      ... 중간생략
      NLJ_BATCHING(@"SEL$2"
"S"@"SEL$2")
      END_OUTLINE_DATA
  */

궁하면 통한다.
이상하지 않은가? 테이블이 2개 인데 Nested Loop Join 이 하나가 아닌 2개가 되어버렸다. 또한 NLJ_PREFETCH 힌트가 사라지고 NLJ_BATCHING 힌트로 대체 되었다.
이러한 현상이 의미하는 바는 무엇일까?
9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
드디어 11g 에서 index scan 시 Vector IO 가 가능해졌다. 궁하면 통한다고 했던가? 오라클이 Nested Loop Join 에 대하여 지속적으로 개선해왔다는것을 알수있다.
참고로 NO_NLJ_BATCHING 힌트를 사용하면 9i/10g 의 Plan 으로 돌아가게 된다.

그러면 11g 의 버젼에서 v$sesstat 통계를 보자.

NAME                                           DIFF
---------------------------------------- ----------
Batched IO vector block count                  3758
Batched IO vector read count                     50
... 이후 생략

위에서 보듯이 Batched IO 란것이 생겼다.
Batched IO (혹은 Vector IO) 기능에 힘입어 table prefetch 에 이어서 11g 에서는 index scan 의 성능까지 향상되었다.

주의사항 : 위에서 수행한 모든 테스트는 Physical read 시에만 해당된다. 위의 예제 스크립트에 buffer cache 를 flush 한 이유도 여기에 있다.

결론:
  오라클 9i, 10g 및 11g 에서 개선된 Nested Loop Join 의 원리는 다음과 같다.
첫번째는 9I/10g 에서 후행 테이블의 TABLE ACCESS BY INDEX ROWID Operation 작업속도가 개선되었다는것과 두번째로 11g 에서 후행 테이블의 인덱스 scan 속도까지 획기적으로 개선되었다는 것이다. 이것은 table prefetch 기능과 Vector I/O 기능에 의해서 각각 구현 되었다. 이기능들과 별도로 이글의 서두에서 이야기한 buffer pinning 기능까지 덤으로 따라오게 되었다.

  앞으로 11g 를 사용시 과거처럼 Batch 용 SQL 에서 무조건 hash 조인을 남발하지 말았으면 한다.
조인건수가 많지 않고 후행 테이블에 적당한 인덱스가 있을 경우에 최소한 Nested Loop Join 과 성능비교를 해보아야 하지않을까?

<편집후기 : 위 테스트는 11g 에서 테스트 되었음을 다시한번 밝혀둡니다.>

Posted by extremedb

댓글을 달아 주세요

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

    테스트하실 때 10g의 정확한 하위 버전이 어떻게 되나요? 그 버전부터 NLJ_PREFETCH Hint와 NO_NLJ_PREFETCH Hint가 추가되었군요!

    11g에서는 NLJ_BATCHING, NO_NLJ_BATCHING Hint가 추가된 것 같습니다. Nested Loop Join과 관련된 동작 방식을 이제 거의 완벽하게 Hint로 제어할 수 있게 되었군요.

    11g에서 V$SQL_HINT view를 보면 아래와 같이 Hint가 등록되어 있네요.

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_BATCHING NO_NLJ_BATCHING 11.1.0.6
    NO_NLJ_BATCHING NLJ_BATCHING 11.1.0.6

    NAME INVERSE VERSION
    -------------------- -------------------- --------------------
    NLJ_PREFETCH NO_NLJ_PREFETCH 11.1.0.6
    NO_NLJ_PREFETCH NLJ_PREFETCH 11.1.0.6

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.11 09:59 신고  댓글주소  수정/삭제  댓글쓰기

    오타에 의해서 10g 에서 수행되었다고 적었네요.
    수정 하도록 하겟습니다.
    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다.
    역 힌트도 마찬 가지 입니다.
    또한 11g 에서도 NO_NLJ_BATCHING, NO_NLJ_PREFETCH 힌트를 같이사용하면 작동을 하지 않는것이 발견 되었습니다.
    따라서 11g 에서도 9i~11g 에서 발전된 모든 기능을 해제 하려면 파라미터를 사용하는수 밖에 없습니다.

  3. 바우 2009.05.11 18:00  댓글주소  수정/삭제  댓글쓰기

    올려주신 글 잘 읽어보고 있습니다. 이번 글에서 의문점이 생겨서 문의를 드립니다.

    9i/10g 에서 table prefetch 기능이 추가되었지만 index scan 에 관해서는 그런기능이 없었다.
    이 부분이 잘 이해가 안됩니다.

    실행계획에서 보면 먼저 테이블 FULL로 읽고 인덱스 RANGE SCAN할때 여러개를 묶어서 하는 것이라고 이해가 되었습니다. 그렇다면 FULL SCAN이 아니라 인덱스 SCAN하고 테이블 갈때 한다는 것인데 INDEX SCAN에 관해서는 그런 기능이 없었다라고 하시니 헷갈립니다. 이것에 대해서 자세히 설명을 해주실 수 있을런지요.
    그리고 여러개라고 하셨는데 그게 몇개인지 알 수 없나요? 그리고 만약 그 몇개를 인위적으로 늘린다면 NL조인으로 부분범위 처리를 하고자 할때 성능 차이가 생길 수 있을 것 같은데 실제로 그렇게 되는지요. 궁금합니다.

    올려주신 글 항상 고맙게 잘 보고 있습니다. 감사합니다.

  4. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.11 21:17 신고  댓글주소  수정/삭제  댓글쓰기

    먼저 "9i/10g 에서 table prefetch 기능이 추가되었다" 함은 TABLE ACCESS BY INDEX ROWID 부분을 두고 하는말입니다. 제가 9i/10g plan 에 파랑색으로 표시한 부분입니다. 그부분은 index scan 이 아니지요.
    index scan 에 의한 부분은 11g 에 나옵니다. 내부적인 NLJ_BATCHING 힌트나 Batched IO vector block count 등이 그증거가 되겟습니다.
    또한 table prefetch 시에 운반단위수의 수정을 말씀해주셨는데 아주 위험 합니다.
    물론 그런기능을 하는 히든 파라미터가 존재 합니다.
    하지만 그것을 수정했을경우 나타나는 버그에 대해서는 누구도 알수 없습니다.
    검증된바가 없습니다.
    따라서 오라클사의 직원들조차 그 파라미터들을 건드리지 않습니다.
    건드리는 경우는 버그에 의해서 기능을 못쓰도록 막을때나 사용합니다.
    하지만 이또한 위험 하므로 히든파라미터는 그대로 놓고 쓰시는것이 제일 좋습니다.
    http://scidb.tistory.com/entry/히든-파라미터-설정의-위험성 를 참조 하시기 바랍니다.

  5. 바우 2009.05.12 09:11  댓글주소  수정/삭제  댓글쓰기

    답변 감사드립니다. ^^

  6. 야함나르 2009.05.18 16:14  댓글주소  수정/삭제  댓글쓰기

    NLJ_BATCHING , NLJ_PREFETCH 힌트등은 11g 에서만 사용하실수 있습니다 라고 되어 있는데
    제가 보고 있는 DB의 경우 9.2.0.7 버전에서 8i와 같은 실행 계획이 나타나고 있습니다. 9 버전에는 힌트가 없다면 파라미터 조정을 통한 방법 말고 table prefetch를 유도하는 방법은 없을까요?.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.18 20:09 신고  댓글주소  수정/삭제

      파라미터를 조정하기 전에 table prefetch 기능은
      제약사항이 있으므로 먼저 nested loop join 의 형태를 점검 하셔야 합니다.
      1.nested loop join 일때만 가능함.
      2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함.
      3.후행집합이 index range scan 일때만 가능함.
      위의 3가지 조건을 만족하는지 먼저 살펴보시기 바랍니다.

    • 야함나르 2009.05.19 11:44  댓글주소  수정/삭제

      네 위의 조건을 만족합니다.

      --------------------------------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE
      1 0 SORT (AGGREGATE)
      2 1 NESTED LOOPS
      3 2 TABLE ACCESS (FULL) OF 'WRITTEN'
      4 2 TABLE ACCESS (BY INDEX ROWID) OF 'PRODUCT'
      5 4 INDEX (UNIQUE SCAN) OF 'PRODUCT_PK' (UNIQUE)

      이런 플랜이 생성되어서 적용할수 있지 않을까 합니다만.

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

      보여주신 plan 은 3번 째 조건을 만족 하지 못합니다.
      후행 테이블이 index unique scan 이므로 동시에 여러건을 가져오지 못합니다. index range scan 일때만 가능 합니다.

    • 야함나르 2009.05.19 13:10  댓글주소  수정/삭제

      아; 그렇군요 ^^.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.28 16:10 신고  댓글주소  수정/삭제

      과연 그렇군요.
      간단하지만 매우 유용한 테스트 입니다.
      좋은 정보 감사드립니다.^^

    • table prefetch 제약사항 점검.. 2009.08.28 16:23  댓글주소  수정/삭제

      table prefetch 제약사항 중 "2.선행집합이 index range scan 혹은 full table scan 이어야만 가능함"에 관련하여 테스트를 해 보았습니다.
      결론은 index range scan 뿐만 아니라 index full scan, fsat full scan, skip scan에서도 발생하는 군요..
      (MIX/MAX)에서는 table prefetch가 불가한테 descending + rownum으로 하니 가능하네요...

      --스크립트 (욱짜님 스크립)
      drop table t1 purge;
      --drop table t1;

      drop table t2 purge;
      --drop table t2;

      create table t1(c1 int, c2 int);
      create table t2(c1 int, c2 int);

      --create index t1_n1 on t1(c1);
      alter table T1 add constraint T1_PK primary key(c1);
      create index t1_n2 on t1(c2,c1);
      create index t2_n1 on t2(c1);

      insert into t1
      select rownum, round(rownum,-2)
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      insert into t2
      select rownum, rownum
      from all_objects, all_objects
      where rownum <= 10000
      ;
      commit;

      EXEC dbms_stats.gather_table_stats(user, 'T1', cascade => true);
      EXEC dbms_stats.gather_table_stats(user, 'T2', cascade => true);


      -- 테스트 결과.
      SELECT /*+ USE_NL(T1 T2) LEADING(T1) INDEX(T2) */
      COUNT(T2.C2)
      FROM
      --CASE 01. INDEX UNIQUE SCAN(X)
      --(SELECT C1 FROM T1 WHERE C1 = 1256) T1 , T2

      --CASE 02. INDEX RANGE SCAN(O)
      --(SELECT /*+ INDEX_RS(T1) */ C1 FROM T1 WHERE C1 <= 1256) T1 , T2

      --CASE 03 INDEX RANGE SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 04 INDEX RANGE SCAN (MIN/MAX)(X)
      --(SELECT /*+ INDEX_RS(T1) */ MAX(C1) C1 FROM T1 WHERE C1 <= 1000 ) T1 , T2

      --CASE 05 INDEX FULL SCAN(N/A)
      --(SELECT C1 FROM T1 ) T1 , T2

      --CASE 06. INDEX FULL SCAN (MIN/MAX)(X)
      --(SELECT MAX(C1) C1 FROM T1 ) T1 , T2

      --CASE 07 INDEX FULL SCAN DESCENDING(O)
      --(SELECT /*+ INDEX_DESC(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 08 INDEX FAST FULL SCAN(O)
      --(SELECT /*+ INDEX_FFS(T1) */ C1 FROM T1 ) T1 , T2

      --CASE 09 INDEX SKIP SCAN(O)
      (SELECT /*+ INDEX_SS(T1 T1_N2) */ C1 FROM T1 ) T1 , T2
      WHERE T1.C1 = T2.C1 ;

  7. 2009.07.22 11:32  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 좋은 글 항상 감사합니다.
    다시 보다보니 궁금한 점이 있어서 질문드립니다.

    1. 기존 nested loop은 outer테이블에서 출발, inner인덱스에 엑세스하고 inner테이블에 엑세스해서 해당 정보까지 가져오는데 반해 소위 advanced nested loop(기존 NL과 비교해서 이걸 뭐라고 해야할지 모르겠습니다.)는 outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합의 rowid로 inner테이블에 엑세스하기 때문에 outer테이블의 크기에 의해 결과반환시간(first_rows)에 영향을 줄 것 같고 결과적으로 기존의 부분처리와는 차이가 있을 것 같습니다.
    (빠른 결과를 위해 운반단위가 다차는 순간 테이블블록에 엑세스한다는 것에 생각해봤지만 반복적인 i/o를 줄이는 효과를 감소시키는 결과라서 이건 아닐 것같은 생각이 들고요.)
    만약 제 생각이 맞다면 어떤 식으로 테스트하면 두 차이를 확연히 드러낼 수 있을까요?

    2. outer테이블의 스캔이 끝난 이후 inner인덱스과 join한 중간집합은 inner인덱스의 rowid로 정렬되어 inner테이블 엑세스 시 logical i/o와 physical i/o를 줄일 수 있다고 이해하고 있습니다.
    그런데 중간집합이 inner인덱스의 rowid로 정렬된다는 것은 어떻게 확인 할 수 있을까요?
    실행계획에는 중간집합정렬에 관한 단서는 찾을 수 없는데..제가 잘못 이해하고 있는 부분이 있나요?


    질문이 장황하지만 제가 잘못 이해하고 있는 부분이 있다면 지적해주시고
    합당한 질문이라면 명쾌한 답을 주시면 감사하겠습니 다. ^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.07.23 09:58 신고  댓글주소  수정/삭제

      답변이 늦어져서 죄송합니다.
      1번에 대한 답변입니다.
      prefetch 라는 개념은 후행집합이 index range scan 으로 풀리므로 어짜피 scan 해야할 block 이 정해져 있다고 볼수 있습니다. 이때 Range 범위 내에서 Single index Range Scan 을 하는것 보다는 여러개의 Process 혹은 Thread 가 Index range Scan 을 하는 것이 성능상 유리하기 때문에 생긴 개념입니다.
      하지만 이것의 특징은 Hit ratio 가 있다는 점이지요.
      Prefetch 된 Block 들의 데이터를 사용하지 않을수 있기 때문입니다. 이 Hit Ratio 가 떨어지면 Prefetch 의 효과는 감소 됩니다.
      테스트를 수행시 v$sesstat 에서 SQL 수행전과 수행후의 value 의 차이를 모니터링 하시고 이때 나온 값들중에 Hit ratio 로 판단되는 값들이 있었습니다.
      이부분을 중점적으로 테스트 해보시는 것이 좋을거 같습니다.

      두번째 답변입니다.
      inner 테이블의 데이터는 inner 테이블의 인덱스 rowid 로 절렬이 되어 있지 않습니다. 그리고 실제로 정렬이 수행되지 않습니다. 물론 인덱스의 경우 데이터가 같은 값이라면 rowid 로 정렬리 되어 있기는 합니다.

      prefetch 시에 정렬을 하는 기능은 없는것으로 생각 하시면 됩니다.

  8. 2009.07.23 19:07  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다. (목이 좀 늘어났습니다 ㅎㅎ)
    저는 운반단위로 블럭에 엑세스하면 반복적인 i/o가 발생할 수 있어서 비효율적이고 그러니까 그렇게 처리하지 않을거야라고 생각했었는데 결과적으로 아닐거라고 생각한대로 동작하는군요.

    요즘 cost based oracle fundamentals(비투엔)을 읽고 있습니다. 그중 table prefetching의 동작을 설명하는 부분이 있는데 그중 "결과집합을 rowid순으로 정렬하고 나서 테이블 액세스를 시도하기 때문에 테이블 길이를 따라 블록들을 단 한 번씩만 방문하면 된다."(p386) 라는 글이 있습니다.
    이 부분때문에 여러가지로 궁리하게 되었는데 좀더 궁리해봐야겠습니다.

    답변 감사합니다. ^^

    • 브라이언홍 2009.08.27 15:44  댓글주소  수정/삭제

      http://ukja.tistory.com/166
      에서 댓글 3번에 이런 글이 있네요...
      Jonathan Lewis가 [CBO Fundamentals]에서 "Table Prefetching"이라는 용어를 사용했다고 하셨는데, 제가 볼 때는 이 책에서도 새로운 NL Join 메커니즘을 잘못 설명하고 있다고 여겨집니다(번역서에서 마침 그 부분을 제가 번역했는데, 제가 알고 있는 내용과 달라 많은 고민을 했었죠.)

  9. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.08.27 16:29 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사드립니다.

<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" 사실 이 용어 자체가 저한텐 아직 낯설군요..

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