'Subquery Flattening'에 해당되는 글 2건

  1. 2009.04.20 Query Transformation Internal ( about JPPD using Lateral View )
  2. 2008.09.09 Using Sub query Method (Sub query Flattening ) 10
  2007년 가을이었던가? 사내 컨설턴트로 부터 조금 어려운 질문이 들어왔다.
"다른종류의 쿼리변환은 모두 내가 수동으로 쿼리를 만들수 있습니다. 하지만 JPPD 는 쿼리를 만들수 없습니다.
혹시 오라클이 JPPD 를 적용한 SQL 을 보여줄수 있는지요?"

  JPPD 는 수도없이 봐왔지만 막상 개념을 적용한 SQL 을 내손으로 작성하려 하니 전혀 작성할수가 없었다.
여러분이 알만한 미국및 영국의 유명한 컨설턴트들과 접촉을 해보았지만 역시 그들도 마찬가지였다. 필자는 이문제로 1주간 고생을한 끝에 직접 원리를 알아내었다. 따라서 어떤 메뉴얼에도 어떤 튜닝책에도 이런 이야기는 없음을 먼저 밝혀둔다.

JPPD ? 그게뭐야?
  쿼리변환의 중요성을 알았으므로 이제 쿼리변환중에 가장 자주나오는 Unnesting 과 JPPD 의 개념을 알아보자.
Unnesting 이란 서브쿼리를 인라인뷰로 만들어 from 절로 끌어올리는 쿼리변환을 의미한다. JPPD 란 (Join Predicate Push Down)의 약자로서 인라인뷰 외부의 조인 조건이 인라인뷰 안쪽으로 파고드는 것을 의미한다.
물론 인라인뷰는 대신에 뷰로 사용해도 마찬가지 이다.

그럼 쿼리변환을 한번 해보자.
  지난번 오라클에 트랜스포머가 있다? 라는 글에서 다단계 쿼리변환(Unnesting + View Merging) 사례를 설명한바 있다. 이번에는 다단계 쿼리변환 이면서 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD가 수행되는 사례를 알아보자.

환경 : Oracle 10.2.0.4

select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
       outer.*
 from (SELECT * FROM emp outer
         UNION ALL
         SELECT * FROM emp outer) OUTER
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */  avg(inner.sal)
                              from emp inner
                           where inner.deptno = outer.deptno
                          ) ;

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |           |      1 |     10 |00:00:00.03 |      27 |
|   2 |   VIEW                         | VW_SQ_1   |      1 |      5 |00:00:00.02 |       7 |
|   3 |    HASH GROUP BY               |           |      1 |      5 |00:00:00.02 |       7 |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |00:00:00.02 |       7 |
|   5 |   VIEW                         |           |      5 |     10 |00:00:00.01 |      20 |
|   6 |    UNION ALL PUSHED PREDICATE  |           |      5 |     10 |00:00:00.01 |      20 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |      11 |
|*  8 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       5 |
|*  9 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |       9 |
|* 10 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
  10 - access("OUTER"."DEPTNO"="DEPTNO")

위실행계획은 쿼리변환이 2단계로 쿼리변환이 수행되었다.
지금부터 과정을 하나하나 살펴보자.

1.단계 : Unnesting 수행
 먼저 서브쿼리가 인라인뷰로 바뀌어 from 절로 올라간다.
그리고 쿼리의 바깥쪽에 WHERE 조건이 생성되며 조인절도 생성된다.
이것은 아래의 쿼리와 같다.

select /*+ gather_plan_statistics LEADING(SUB OUTER) USE_NL(OUTER) NO_PUSH_PRED(OUTER) */
       outer.*
 from (SELECT * FROM SI31041.emp outer                    --> JPPD not yet
       UNION ALL
       SELECT * FROM SI31041.emp outer) OUTER ,
       ( select deptno, avg(sal) AS VW_COL_1
          from emp
         group by deptno
       ) SUB
where outer.sal > SUB.VW_COL_1
   and outer.deptno = SUB.deptno ;


------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------
|   1 |  NESTED LOOPS        |      |      1 |     12 |00:00:00.01 |      50 |
|   2 |   VIEW               |      |      1 |      3 |00:00:00.01 |       7 |
|   3 |    HASH GROUP BY     |      |      1 |      3 |00:00:00.01 |       7 |
|   4 |     TABLE ACCESS FULL| EMP  |      1 |  
  14 |00:00:00.01 |       7 |
|*  5 |   VIEW               |      |      3 |     12 |00:00:00.01 |      43 |
|   6 |    UNION-ALL         |      |      3 |     84 |00:00:00.01 |      43 |
|   7 |     TABLE ACCESS FULL| EMP  |      3 |     42 |00:00:00.01 |      22 |
|   8 |     TABLE ACCESS FULL| EMP  |      3 |     42 |00:00:00.01 |      21 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("OUTER"."SAL">"SUB"."VW_COL_1" AND "OUTER"."DEPTNO"="SUB"."DEPTNO"))

위의 Predicate Information을 보면 서브쿼리가 인라인뷰로 바뀌었지만 아직 인라인뷰 내로 WHERE 조건및
조인조건이 파고들지 못했다. 따라서 뷰를 만들고 FILTER 처리가 된것이다.

2단계: JPPD 수행
조인조건및 WHERE 조건이 UNION ALL 로 분리된 각각의 SQL 에 파고든다. 
최종적으로 완성된 쿼리는 아래와 같다.


select /*+ push_pred (outer) */
        outer.*
  from  (select /*+ unnest qb_name (sub) */
                avg(inner.sal) vw_col_1,inner.deptno deptno
           from emp inner
          group by inner.deptno
        ) vw_sq_1,
        (  select *                            --> JPPD OK  using lateral view
             from emp outer
            where outer.deptno=vw_sq_1.deptno -->  조건이 인라인뷰 내로 들어옴 (Lateral view)
            and outer.sal>vw_sq_1.vw_col_1
            union all 
           select *
             from emp outer
            where outer.deptno=vw_sq_1.deptno  --> 조건이 인라인뷰 내로 들어옴(Lateral view)
            and outer.sal>vw_sq_1.vw_col_1
        ) outer


JPPD 의 비밀이 풀리다!
  위의 SQL 에서 이상한점을 발견할수 있는가?
인라인뷰 OUTER 에서 다른 인라인뷰 VW_SQ_1 의 컬럼을 참조하고 있다. 이것은 놀라운 일이다. 인라인뷰 내에서 마치 스칼라 서브쿼리처럼 from 절의 다른 테이블 혹은 다른 인라인뷰의 정보를 사용한것이다. 바로 이것이 Lateral View 의 개념이다. 다시말하면 Lateral View란 스칼라 서브쿼리처럼 사용할수 있는 "스칼라 인라인뷰" 라고 생각하면 된다. 위의 SQL 을 보면 아래의 실행계획과 같을수 밖에 없다는것을 알수 있다.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |           |      1 |     10 |00:00:00.03 |      27 |
|   2 |   VIEW                         | VW_SQ_1   |      1 |      5 |00:00:00.02 |       7 |
|   3 |    HASH GROUP BY               |           |      1 |      5 |00:00:00.02 |       7 |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |00:00:00.02 |       7 |
|   5 |   VIEW                         |           |      5 |     10 |00:00:00.01 |      20 |
|   6 |    UNION ALL PUSHED PREDICATE  |           |      5 |     10 |00:00:00.01 |      20 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |      11 |
|*  8 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       5 |
|*  9 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |       9 |
|* 10 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
  10 - access("OUTER"."DEPTNO"="DEPTNO")

오라클만이 Lateral View를 사용할수 있다.
  아쉽게도 Lateral View 는 오라클만이 내부적으로 사용할수 있다. 필자나 여러분이 사용할 경우 에러가 발생한다. 그렇다면 위의 SQL은 어디서 나온것인가?  그것은 쿼리 트랜스포머의 쿼리변환작업을 10053 이벤트를 이용하여 Trace 파일에서 추출한 결과 이다.

결론:
 이상으로 우리는 2가지 사실을 알아내었다.
첫번째는 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD를 시도한다는것.
두번째는 쿼리 트랜스포머는 JPPD 수행시 Lateral View를 사용한다는것이다.
마지막으로 가까운 미래에 Lateral View를 User가 직접 사용할수 있는날을 기대하면서 이글을 마치고자 한다.

Further Reading : 
Lateral View 개념  : http://scidb.tistory.com/search/lateral%20view
SubQuery Unnesting : http://scidb.tistory.com/entry/SubQuery-Using-Method-1

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
,