'semi join'에 해당되는 글 3건

  1. 2009.08.31 Semi Join 의 재조명 (5)
  2. 2009.03.02 Hash Join Right (Semi/Anti/Outer) 의 용도 (17)
  3. 2008.09.09 Using Sub query Method (Sub query Flattening ) (10)

예전에 Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 서브쿼리가 Semi Join 으로 적용될때 조인의 순서를 바꿀수 있는 방법에 대하여 설명한바 있다. 이어서 오늘도 예전글과 유사한 Semi Join 의 튜닝에 대해서 이야기 하려 한다.

환경 : Oracle 11.1.0.6
먼저 테이블을 단순한 구조로 만들기 위해서 테이블을 2개 생성한다.
ORACLE 의 Sample 스키마인 SH 를 이용한다.

CREATE TABLE SALES_T AS SELECT * FROM sh.sales;

CREATE TABLE PRODUCTS_T AS SELECT * FROM sh.products ;

ALTER TABLE PRODUCTS_T ADD CONSTRAINT PK_PRODUCTS_T         --> PK 및 인덱스 생성
PRIMARY KEY (PROD_ID) USING INDEX;

CREATE BITMAP INDEX SALES_T_PROD_BIX ON SALES_T (PROD_ID);  --> BIT MAP 인덱스 생성

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

이제 SQL 을 실행할 준비가 다 되었다.
아래는 상품의 Category 가 Software/Other 인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL 이다.
이러한 SQL 은 주로 DW 에서 나타난다.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
            s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
 WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                                             p.prod_id
                                   FROM products_t p
                                 WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id ;

--------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts |   A-Time   | Buffers |
--------------------------------------------------------------------------
|   1 |  HASH GROUP BY      |            |      1 |00:00:08.12 |    4445 |
|*  2 |   HASH JOIN         |            |      1 |00:00:06.94 |    4445 |
|*  3 |    TABLE ACCESS FULL| PRODUCTS_T |      1 |00:00:00.01 |       4 |
|   4 |    TABLE ACCESS FULL| SALES_T    |      1 |00:00:01.84 |    4441 |
--------------------------------------------------------------------------

헉 8초 이상 걸렸다!
Subquery Unnesting 이 발생하여 작은쪽 테이블인 Products_t 가 Driving 테이블로 선정되었고 Salse_t 테이블과 Hash Join 을 하게된다. Scan 한 블럭수는 4445블럭 이며 8초 이상 수행되었다. 조인건수(Starts 항목 참조)는 1회에 불과하다. 이것은 최적으로 튜닝된 SQL 인가?  이것을 알아보기 위해 Hash Join 대신 Nested Loop 조인으로 바꿔보자.

  SELECT /*+ GATHER_PLAN_STATISTICS */
               prod_id, channel_id, SUM (qs) AS qs, SUM (amt) amt
    FROM (SELECT /*+ NO_MERGE USE_NL(PR S) */
                            s.prod_id, s.channel_id,
                            quantity_sold AS qs, amount_sold AS amt
                  FROM sales_t s, products_t pr
                 WHERE s.prod_id = pr.prod_id
                     AND pr.prod_category_desc = 'Photo')
GROUP BY prod_id, channel_id ;

원본 SQL 의 경우 왠만해서는 Nested Loop Join 으로 바뀌지 않으므로 강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join 으로 유도 하였다.

------------------------------------------------------------------------------------
| Id |Operation                       |Name            |Starts|   A-Time   |Buffers|
------------------------------------------------------------------------------------
|  1 | HASH GROUP BY                  |                |    1 |00:00:10.07 |   3099|
|  2 |  VIEW                          |                |    1 |00:00:08.92 |   3099|
|  3 |   NESTED LOOPS                 |                |    1 |00:00:07.70 |   3099|
|  4 |    NESTED LOOPS                |                |    1 |00:00:02.03 |     41|
|* 5 |     TABLE ACCESS FULL          |PRODUCTS_T      |    1 |00:00:00.01 |      4|
|  6 |     BITMAP CONVERSION TO ROWIDS|                |   26 |00:00:00.41 |     37|
|* 7 |      BITMAP INDEX SINGLE VALUE |SALES_T_PROD_BIX|   26 |00:00:00.01 |     37|
|  8 |    TABLE ACCESS BY INDEX ROWID |SALES_T         |  405K|00:00:02.45 |   3058|
------------------------------------------------------------------------------------
 

Hash Join 보다 더 느려졌다
Nested Loop Join 으로 유도 하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러난다. 먼저 Nested Loop Join 이 40만번 이상 수행된다.(Starts항목 참조) 그결과로 View 를 만드는데 8초 이상 수행되었다. Buffer 가 30% 이상 줄어들었지만 조인건수 때문에 시간이 많이 걸리므로 이것은 견딜수 없는 결과이다.

그럼 Hash Join 에 만족해야 하나?
지금까지의 상식으로는 그렇다. 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질것이다.

Driving Semi Join 이란?
Driving Semi Join 이란 Semi Join 의 변형된 형태로서 Hash Join 의 장점(조인건수의 최소화)과 Nested Loop Join 의 장점(선행테이블이 상수화 되어 후행 테이블에서 인덱스를 효율적으로 사용) 을 합친 개념이다. 물론 Driving Semi Join 이라는 Title 자체의 의미대로 Semi Join 이 Driving 집합이 되는것은 당연하다. 다음의 SQL 을 보자  

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
             s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                                          p.prod_id
                                FROM products_t p
                               WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id ;


----------------------------------------------------------------------------------
| Id | Operation                     |Name            |Starts|   A-Time  |Buffers|
----------------------------------------------------------------------------------
|   1|  HASH GROUP BY                |                |     1|00:00:03.40|   2231|
|   2|   TABLE ACCESS BY INDEX ROWID |SALES_T         |     1|00:00:02.04|   2231|
|   3|    BITMAP CONVERSION TO ROWIDS|                |     1|00:00:00.42|     63|
|   4|     BITMAP MERGE              |                |     1|00:00:00.02|     63|
|   5|      BITMAP KEY ITERATION  |                |     1|00:00:00.02|     63|
|*  6|       TABLE ACCESS FULL       |PRODUCTS_T      |     1|00:00:00.01|      4|
|*  7|       BITMAP INDEX RANGE SCAN |SALES_T_PROD_BIX|    26|00:00:00.02|     59|
----------------------------------------------------------------------------------


환상적이다

Hash Join (8초 이상), Nested Loop Join (10초 이상) 인데 반하여 Driving Semi Join 은 3.4초 만에 끝이 났다. 또한 조인횟수도 26 번에 불과하며 I/O Block 수도 2231 로 Hash Join 의 절반에 해당한다.

환상적인 이유는?
그 이유는 Join 대신에 BITMAP KEY ITERATION 이 수행된 까닭이다.
BITMAP KEY ITERATION 의 원리는
INLIST ITERATOR Operation 과 같다고 볼수 있다. 
INLIST ITERATOR 의 개념은 InList / Concatnation / Range Scan Control 하기 을 참조하라.

Driving Semi Join은 B-Tree 인덱스에서도 사용이 가능해
이기능은 Bit Map 인덱스 뿐만 아니라 B-Tree 인덱스 에서도 사용가능하다.
하지만 _b_tree_bitmap_plans 파라미터가 True 로 되어 있어야 한다.
아래의 SQL 을 보자.


DROP INDEX SALES_T_PROD_BIX;  --> Bit Map 인덱스 Drop

CREATE INDEX SALES_T_PROD_IX ON HR.SALES_T (PROD_ID); --> B-Tree 인덱스 생성

ALTER SESSION SET  "_b_tree_bitmap_plans" = TRUE;

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
     s.prod_id, s.channel_id, SUM (quantity_sold) AS qs,
     SUM (amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
                          p.prod_id
                     FROM products_t p
                    WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id;


-------------------------------------------------------------------------------------
| Id |Operation                          |Name           |Starts|   A-Time  |Buffers|
-------------------------------------------------------------------------------------
|   1| HASH GROUP BY                     |               |     1|00:00:04.94|   3045|
|   2|  TABLE ACCESS BY INDEX ROWID      |SALES_T        |     1|00:00:03.76|   3045|
|   3|   BITMAP CONVERSION TO ROWIDS     |               |     1|00:00:02.14|    877|
|   4|    BITMAP MERGE                   |               |     1|00:00:01.73|    877|
|   5|     BITMAP KEY ITERATION          |               |     1|00:00:01.73|    877|
|*  6|      TABLE ACCESS FULL            |PRODUCTS_T     |     1|00:00:00.01|      4|
|   7|      BITMAP CONVERSION FROM ROWIDS|               |    26|00:00:01.73|    873|
|*  8|       INDEX RANGE SCAN            |SALES_T_PROD_IX|    26|00:00:00.86|    873|
-------------------------------------------------------------------------------------

Bit Map 인덱스에 비하여 약간의 비효율이 있다. 그것은 BITMAP CONVERSION FROM ROWIDS Operation 이 추가되었기 때문이다. 하지만 기존의 Hash Join 이나 Nested Loop Join 보다는 수행시간, I/O Block 수, 조인건수등이 우월함을 알수 있다.

당신은 이미 Driving Semi Join을 사용했다
여러분의 SQL 이 Star Transformation 을 수행하는 경우는 싫든 좋든 Driving Semi Join 기능을 이용하고 있는 것이다. 아래는 Star Transformation 을 수행하는 SQL 의 Plan 과 Outline Data 를 출력한 것이다.


  SELECT /*+ gather_plan_statistics */
         p.prod_id,
         c.CHANNEL_ID,
         SUM (quantity_sold) AS qs,
         SUM (amount_sold) AS amt
    FROM sh.sales s, sh.CHANNELS c, sh.products p
   WHERE s.CHANNEL_ID = c.CHANNEL_ID
     AND c.CHANNEL_DESC = 'Internet'
     AND s.prod_id = p.prod_id
     AND p.prod_category_desc = 'Photo'
GROUP BY p.prod_id, c.CHANNEL_ID;

------------------------------------------------------------------+
| Id  | Operation                              | Name             |
------------------------------------------------------------------+
| 0   | SELECT STATEMENT                       |                  |
| 1   |  HASH GROUP BY                         |                  |
| 2   |   HASH JOIN                            |                  |
| 3   |    TABLE ACCESS FULL                   | CHANNELS         |
| 4   |    HASH JOIN                           |                  |
| 5   |     TABLE ACCESS FULL                  | PRODUCTS         |
| 6   |     PARTITION RANGE ALL                |                  |
| 7   |      TABLE ACCESS BY LOCAL INDEX ROWID | SALES            |
| 8   |       BITMAP CONVERSION TO ROWIDS      |                  |
| 9   |        BITMAP AND                      |                  |
| 10  |         BITMAP MERGE                   |                  |
| 11  |          BITMAP KEY ITERATION          |                  |
| 12  |           BUFFER SORT                  |                  |
| 13  |            TABLE ACCESS FULL           | CHANNELS         |
| 14  |           BITMAP INDEX RANGE SCAN      | SALES_CHANNEL_BIX|

| 15  |         BITMAP MERGE                   |                  |
| 16  |          BITMAP KEY ITERATION          |                  |
| 17  |           BUFFER SORT                  |                  |
| 18  |            TABLE ACCESS FULL           | PRODUCTS         |
| 19  |           BITMAP INDEX RANGE SCAN      | SALES_PROD_BIX   |

------------------------------------------------------------------+

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
      ... 중간생략    
      STAR_TRANSFORMATION(@"SEL$1"
"S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
      BITMAP_TREE(@"SEL$1"
"S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
     
SEMIJOIN_DRIVER(@"SEL$1898F719")
      SEMIJOIN_DRIVER(@"SEL$D750A531")
    END_OUTLINE_DATA
  */

Driving Semi Join의 장점은 또 있다
Driving Semi Join Nested Loop Join 시의 조인횟수에 의한 부하나 Hash 조인시의 Full Table Scan + Hashing 작업의 부하를 획기적으로 감소켜준다. 더 좋은것은 Star Transformation 사용시에 바인드 변수를 사용할수 없었지만 Driving Semi Join은 이러한 제약이 없다. 하지만 이 기능은 오라클사의 정식힌트가 아니므로 필자의 경우 데이터 분석 용도로만 사용하고 있다. 이기능이 11.2 버젼에는 정식으로 메뉴얼에 나오길 기대하며 이글을 마칠까 한다.


Posted by extremedb

댓글을 달아 주세요

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

    재미있는 내용입니다.

    Hint가 소개된 시점과 비교해보면 보면 아마도 Star Transformation을 제어하기 위한 용도로 소개된게 아닌가 하는 생각이 드는군요.

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

      8i 에서 나온것을 보면 Star Transformation 의 용도로 나온것이 맞는것 같습니다.
      그런의미에서 본다면 위의 SQL 은 Single-Dimension-Star Transformation 로 불러도 될거 같습니다.^^

  2. feelie 2009.09.02 12:25  댓글주소  수정/삭제  댓글쓰기

    좋은 내용 잘 봤습니다. 내용과는 차이가 있지만
    필터와 세미조인간에 차이에 대한 개념이 명확치가 않네요.
    세미조인으로 실행계획이 풀리면 여러가지 방법으로(NL,HASH) 조인이 가능하다. 메인쿼리가 드라이빙이된다
    필터는 메인 쿼리가 무조건 드라이빙이 된다(nested loop 방식과 유사하다)
    개념적으로만 봐서는 조건이 만족하면 더이상 조인을 진행하지 않기때문에 nested loop 세미조인과 필터는 성능상으로
    별차이가 없다. 단 hash join은 성능상으 차이가 발생할수 있다.
    이정도가 필터와 세미조인의 차이로 생각하면 됩니까?

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

    feelie 님 께서 설명한것은 대부분 맞다고 보시면 됩니다. 하지만 2% 부족 한것이 있습니다.
    Filter 의 Caching 효과 에 의해서 Neste Loop Semi Join 과의 차이가 있을수 있습니다.
    즉 서브쿼리에서 return 되는 distinct 한 값의 갯수가 많다면 semi join 을 선택하면되고 적다면 filter 서브쿼리를 선택 하시면 됩니다.
    이때 주의할것은 서브쿼리에서 distinct 하게 return 되는 값은 서브쿼리와 메인쿼리의 조인컬럼을 기준으로 count 하셔야 합니다.
    ex) select * from a
    where exists (select 1 from b where b.col1 = a.col1 ) 이런 SQL 이 있을경우
    DISTINCT 한 값의 갯수는 SELECT COUNT(DISTINCT COL1) FROM B 가 될것입니다.
    DISTINCT 한값이 적을때 filter 가 좋은 이유는 filter 서브쿼리나 스칼라서브쿼리의 caching 기법 때문에 생기는 차이입니다.
    단 메인쿼리가 초대용량인 경우는 hash join right semi 를 선택하시면 됩니다.

    질문하신 내용은 http://scidb.tistory.com/entry/SubQuery-Using-Method-1 로 가셔서 질문하시는 것이 더 나을뻔 햇습니다.

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

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

튜닝 컨설팅을 하다보면 개발자들이 서브쿼리에 대하여 많은 관심을 보이며 또한 자주 사용하곤 한다.
하지만 정작 튜닝관점및  외형적인 관점에서 서브쿼리에 대하여 정의를 내릴수 있는 사람은 많지않다.  
오늘은 서브쿼리에 대하여 정의를 내려보자.

외형적관점에서 서브쿼리의 종류는 2가지 밖에 없다.

1.Non Corelate 서브쿼리  : (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 없음)
2.Corelate 서브쿼리: (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 있음)
그렇다면 튜닝관점에서 서브쿼리를 어떻게 분류해야 할까?
튜닝관점의 서브쿼리는 아래처럼 5가지로 분류할수 있다.

튜닝관점의 서브쿼리의 분류

1.Unnesting 서브쿼리 : 옵티마이져가 서브쿼리를 조인으로 변형시킴
    1)서브쿼리를 조인으로 바꾸는 방식 (일반적으로 서브쿼리의 테이블이 Driving 이된다.)
    2)오라클은 서브쿼리를 인라인뷰로 바꾸고 서브쿼리 집합이 Distinct 하지 않을경우 Sort Unique 나
        Hash Unique 작업을 추가로 진행한다.
      이는 메인쿼리의 건수를 보존하기 위해서 이다.
    3) 힌트 :유도 힌트 : /*+ unnest */
                               (서브쿼리에 사용하거나 메인쿼리에서 쿼리블럭 힌트(qb_name)를 사용하여야 한다.)
                방지 힌트 : /*+ no_unnest */ (서브쿼리에 사용)
            
2.Semi Join/Anti Join : 옵티마이져가 서브쿼리를 조인으로 변형시킴
    1)서브쿼리를 조인으로 바꾸는 방식 (일반적으로 서브쿼리의 테이블은 Driving 이 되지 못한다.)
    2)이방식은 버젼 8i 부터 사용되었으며 아래에 소개되는  Filter SubQuery 를 발전시킨 형태이다.
       메인쿼리의 값을 상수로 받은다음 서브쿼리쪽 테이블에서 만족하는 건이 하나라도 있으면 다음건으로
       넘어간다.(Filter 처리와 원리가 같음.)
       Unnesting 서브쿼리와 Semi Join 을 같이 보는 사람들이 있는데 이건 잘못된것이다.
       오라클에서 Unnesting 개념과 Semi Join 을 같이 보면 안된다.
       물론 10053 보고서에는 둘다 su(sub query unnesting) 로 나오긴 한다.
       필자는 10053 보고서도 마음에 들지 않는다.
       굳이 같이사용할려고 한다면 "SubQuery Flattening" 이라고 해야 한다.
       이렇게 해야만 Unnesting 과 Semi Join이 헷갈리지 않는다.    
    3)세미조인과 안티조인의 차이는 긍정형 (EXISTS 혹은 IN) 은 세미조인으로 풀리고
       부정형 (NOT EXISTS 혹은 NOT IN) 등은 안티조인으로 풀린다.
       물론 안티조인이 되려면 조인되는 양측의 컬럼이 NOT NULL 이거나 WHERE 절에
       NOT NULL 을 명시해야 한다.
     4) 힌트 :유도 힌트 : use_nl 혹은 use_hash 혹은 use_merge (서브쿼리에 사용 해야한다.)
                                 10g 이전버전에서는 세미조인및 안티조인 힌트가 따로 있음.
                 방지 힌트 : /*+ no_unnest */ --> 특이하게도 방지힌트는 unnest 형식과 같다.


3.Access 서브쿼리 : 쿼리변형이 없음
    1)흔히말하는 제공자 서브쿼리임.(서브쿼리부터 풀려서 메인쿼리에 값이 제공된다.)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없다.
    3)힌트: 특별한 힌트없음.
               다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
               서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있으면됨.
    4) 주의사항: corelate 서브쿼리는 제공자 서브쿼리가 될수 없음.

4.Filter 서브쿼리 : 쿼리변형이 없음
    1)흔히 말하는 확인자 서브쿼리임.(메인쿼리의 값을 제공받아 서브쿼리에서 체크하는 방식임)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없고 Filter 로 나온다.
    3)Filter SubQuery 의 특징은 메인쿼리의 From 절에 있는 모든 테이블을 엑세스후에 가장마지막에
      서브쿼리가 실행된다는 것이다.
    4) 힌트: 특별한 힌트없음.
                다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
                메인쿼리로부터 제공되는 서브쿼리의 조인컬럼에 인덱스가 생성되어 있으면됨.

5.Early Filter 서브쿼리 : 쿼리변형이 없음
    1)Filter SubQuery 와 같은 방식이지만 서브쿼리를 최대한 먼저 실행하여 데이터를 걸러낸다.
    2)힌트 : 메인쿼리에 push_subq 힌트사용 (10g 이후부터는 서브쿼리에 힌트사용해야함)
    3)주의사항: 많은 튜닝책에서 "Push_subq 힌트를 사용하면 제공자 서브쿼리를 유도한다" 라고
                     되어 있으나 이는 잘못된 것이다.
                     push_subq 힌트를 사용하면 확인자 서브쿼리(Filter 서브쿼리)를 유도하지만 최대한
                     먼저 수행된다.


오늘은 5가지 중에서 Query Transformation(쿼리변형)과 관련이 있는 Unnesting 서브쿼리 와
Semi Join/Anti Join 에 대해서 이야기 할것이다.
그럼 1번과 2번을 스크립트로 살펴보자.
 
1.Unnesting 서브쿼리 : (원본쿼리)

select small_vc

from    t1

where   n2 between 10 and 200

and     exists  (select  /*+ unnest */ null

                   from   t2

                  where   t2.no_indexed_column = t1.n1

                    and   t2.n2 = 15) ;      


옵티마이져는 위의 쿼리를 아래의 쿼리로 변형시킨다.(아래의 힌트는 이해를 돕기위한 것임)

select /*+ leading(t2 t1) */ t1.small_vc

from    t1,
        (select distinct t2.no_indexed_column  
           from t2
          where t2.n2 = 15 ) t2

where   t1.n2 between 10 and 200
  and   t1.n1 = t2.no_indexed_column



          

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |     1 |    26 |    24 |

|*  1 |  TABLE ACCESS BY INDEX ROWID   | T1    |     1 |    19 |     2 |

|   2 |   NESTED LOOPS                 |       |     1 |    26 |    24 |

|   3 |    SORT UNIQUE                 |       |     1 |     7 |     2 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2 |

|*  5 |      INDEX RANGE SCAN          | T2_N2 |     1 |       |     1 |

|*  6 |    INDEX RANGE SCAN            | T1_PK |     1 |       |     1 |

------------------------------------------------------------------------

위의 plan 에서 보는 바와 같이 서브쿼리가 Driving 집합이 되었으며 메인쿼리의 집합을 보존하기 위해
Distinct 작업(Sort Unique) 를 실행 하였다.
또한 옵티마이져는 서브쿼리내에 조인되는 컬럼에 인덱스도 없고 선택성도 좋지않으므로 Semi Join 보다는 Unnesting 서브쿼리를 선호한다.


2.Semi Join /Anti Join : (원본쿼리)

select small_vc

from    t1

where   n2 between 10 and 200

and     exists  (select  /*+ use_nl(t1 t2) */ null

                   from   t2

                  where   t2.Indexed_column = t1.n1

                    and   t2.n2 = 15) ;  

옵티마이져는 위의 서브쿼리를 아래의 조인쿼리로 변형시킨다..(아래의 힌트는 이해를 돕기위한 것임)
아래 조인절의 (s) 는 세미조인을 의미한다. (세미조인은 메인쿼리의 건수를 변화시키지 않는다)

select  /*+ leading(t1 t2) */
        t1.small_vc

from    t1,
        t2

where   t1.n1 = t2.Indexed_column(s)
  and   t1.
n2 between 10 and 200
  and   t2.n2 = 15

 

             

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |     1 |    23 |     3 |

|   1 |  NESTED LOOPS SEMI             |       |     1 |    23 |     3 |

|*  2 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |    19 |     2 |

|*  3 |    INDEX RANGE SCAN            | T1_PK |     1 |       |     1 |

|*  4 |   INDEX RANGE SCAN             | T2_N2 |     1 |     4 |     1 |

------------------------------------------------------------------------


위의 plan 에서 보는 바와 같이 서브쿼리의 조인되는 컬럼에 인덱스가 있고 선택성이 좋으면 옵티마이져는
세미조인을 선택한다.
세미조인의 특징은
1)Plan 에 Join 정보가 나오며(위의 경우 Nested Loop) --> 바로 이부분이 Unnesting 서브쿼리와 다르다.
2)Driving 이 되지못하고
3)Filter 서브쿼리처럼 한건만 만족하면 바로 다음건으로 넘어가는 조인이라고 했다.
그렇다면 Filter 서브쿼리와 다른점은?
세미조인과 Filter 서브쿼리의 다른점은 세미조인은 필요에 따라서 Hash Join/Sort Merge Join/Nested Loop Join 등을 골라서 사용할수 있다는 점이 다르다.
Filter 서브쿼리는 선택할수 있는 옵션이 없다.

결론:
오늘은 튜닝관점의 서브쿼리의 5가지 종류 중에서 Unnesting 서브쿼리 와 Semi Join /Anti Join 에 대하여 알아보았다.
서브쿼리의 사용법및 서브쿼리 관련 힌트는 서브쿼리의 이해 뿐만아니라 Query Transformation (쿼리변형)을
이해하기 위해서도 반드시 숙지하여야 한다.
다음 시간에는 쿼리변형이 없는 서브쿼리의 3가지 유형(3,4,5번)에 대하여 심도깊게 이야기 할것이다.

Reference :
1)Query Optimization in Oracle Database10g Release 2(White Paper)
2)COST BASED QUERY TRANSFORMATIONS CONCEPT
   AND ANALYSIS USING 10053 TRACE(Riyaj Shamsudeen)
3)Cost Based Oracle Fundamentals(Jonathan Lewis) with Blog (http://jonathanlewis.wordpress.com)

Posted by extremedb

댓글을 달아 주세요

  1. 백면서생 2008.09.09 10:55  댓글주소  수정/삭제  댓글쓰기

    정리 잘하셨네요.remind 잘 했습니다~
    근데 오타가 보이네요.
    Non Correlated Subq와 Correlate Subq의 내용이 바뀐듯~

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

    제가 요즘 Query Transformation과 관련된 세미나를 준비하고 있는데 참조가 되네요.

  3. 시연 2008.09.09 18:59  댓글주소  수정/삭제  댓글쓰기

    좋은글 잘보고 갑니다. 몰랐던것을 알게됬네요. ^^

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

    조만간 저도 CBQT 에 대하여 연재할 생각을 가지고 있습니다.
    참 욱짜님께는 PARALLEL 모니터링 관련 블로그를 올리겠다는 약속을 지키지 못해 죄송합니다.
    프로젝트를 핑계로 할수 밖에 없네요^^

  5. 금땡이 2008.09.17 09:22  댓글주소  수정/삭제  댓글쓰기

    좋은 자료네요. 덕분에 정리가 잘 되었습니다.

  6. Favicon of https://1ststreet.tistory.com BlogIcon SITD 2011.05.12 17:08 신고  댓글주소  수정/삭제  댓글쓰기

    따로따로 하나씩 공부하니까 정리가 진짜 안되던데, 이렇게 모아두니까 쏘옥쏘옥 들어오네요
    좋은 글 감사드립니다 ^^

  7. 김영석 2013.06.06 12:20  댓글주소  수정/삭제  댓글쓰기

    서브쿼리 unnesting의 미묘한 차이를 잘 보았습니다.
    nested loop semi는 원리가 이해가 되는데,
    hash semi와 sort merge semi는 내부동작을 어떻게 하는지
    감이 잘 오지 않습니다.
    이에 대한 글을 아직 쓰지 않았으면 부탁드립니다.

    감사합니다.

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

      기본적으로 semi join은 hash join, sort merge join, nested loop join 을 사용할 수 있으므로 타 조인과 차이점은 없습니다. 하지만 특징이 있습니다. 서브쿼리가 unnesting 된다고 해도 semi로 풀리면 sort unique 혹은 hash unique가 발생하지 않습니다. 왜냐하면 semi 는 항상 후행집합이 되어 존재유무만 체크하기 때문입니다. 따라서 메인쿼리의 건수를 보존하기 위한 distinct 작업은 필요하지 않지요. 하나의 예외가 hash join right semi 입니다. 이때는 서브쿼리가 선행집합이 됩니다.