'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
,
<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
,

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

외형적관점에서 서브쿼리의 종류는 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
,