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

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