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
,