PAGE 처리 할때나 아니면 부분범위 처리를 할때 우리는 TOP SQL 을 많이 사용한다.
오늘은 rownum 을 이용한 TOP N건 처리시 몇가지 함정이 있으므로  정확한 사용법에 대해서 알아보고자 한다.
먼저  환경을 만들기 위하여 테이블및 인덱스를 생성 한다.
환경 : ORACLE 11.1.0.6

create table scott.big_emp as                --> BIG_EMP 생성   
select rownum as empno, a.ename, a.job, a.mgr, a.hiredate, a.sal + rownum as sal, a.comm, a.deptno
  from scott.emp a,
       (select level as no from dual connect by level <= 2000) b
;

create index scott.ix_emp_01 on scott.big_emp (deptno, sal) --> deptno, sal 로 구성된 결합 인덱스 생성
;

create table scott.new_dept as            --> NEW_DEPT 생성
select a.* , a.deptno as page_count    --> 추가적으로 page_count  컬럼생성
  from scott.dept a
;

alter table scott.new_dept add                                         --> PK 및 UNIQUE 인덱스 생성
( constraint pk_new_dept primary key (deptno) using index );

begin                                                                           --> 통계정보 생성
    dbms_stats.gather_table_stats('scott', 'big_emp', cascade => true);
    dbms_stats.gather_table_stats('scott', 'new_dept', cascade => true);
end;


 EMP 와 DEPT 를 이용하여 테이블및 인덱스를 생성 하였고 new_dept 테이블에는 부서별로 PAGE COUNT 라는 컬럼이 추가적으로 생성 되었다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ e.*
  from  (select /*+ index_desc(e IX_EMP_01) */
                    e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname
           from big_emp e,
                  new_dept d
         where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) e
where rownum < :v_page_count             --> :v_page_count  에 숫자 20을 대입한다.
 ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

20번 부서에 해당하는 사원중에서 급여가 큰순으로 20건만 보겠다는 의미이다.

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |      1 |     20 |00:00:00.01 |       8 |
|   2 |   VIEW                          |             |      1 |     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |      1 |     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------
 

위 PLAN 상의 A-Rows 에 나타나듯이 부서번호 20번에 해당하는 모든건을 엑세스및 조인한것이 아니라
 정확히 BIG_EMP 쪽을 20 건만 scan 하였다.
이것이 COUNT STOPKEY 의 위력이다.
즉 BIG_EMP 테이블에 deptno = 20 조건을 만족하는 건이 아무리 많더라도 20 건만 읽고 끝낸다는 것이다.

 하지만 rownum < :v_page_count 조건대신에 부서별로 PAGE COUNT 를 테이블에 저장 하고
그 값을 이용하여 TOP SQL 을 작성 해야 한다면 이야기가 달라진다.
아래의 SQL 을 보면 부서별로 PAGE COUNT 를 지정 하고 있다.

select deptno, page_count
  from new_dept;

<결과>
DEPTNO PAGE_COUNT
------ ----------
    10         10
    20         20
    30         30
    40         40

20번 부서의 경우 20 건만 보겠다는 의미이다.
이제 NEW_DEPT 테이블의 PAGE_COUNT 값으로 TOP SQL 을 작성해서 실행 해보자.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) a
where rownum < a.page_count ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  COUNT                           |             |      1 |     20 |00:00:00.01 |    6269 |
|*  2 |   FILTER                         |             |      1 |     20 |00:00:00.01 |    6269 |
|   3 |    VIEW                          |             |      1 |  10000 |00:00:00.09 |    6269 |
|   4 |     NESTED LOOPS                 |             |      1 |  10000 |00:00:00.07 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |  10000 |00:00:00.04 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |  10000 |00:00:00.01 |      29 |
-------------------------------------------------------------------------------------------------
 
 어이 없게도 20번 부서에 해당하는 전체건(10000 건)을 다 SCAN 하였다.
COUNT STOPKEY Operation 대신에 FILTER Operation 이 사용되었기 때문이다.
아래처럼 ROWNUM 조건을 인라인뷰 내로 밀어 넣어도 결과는 마찬가지 이다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
            and rownum <= d.page_count            
          order by e.sal desc)
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  VIEW                            |             |  10071 |  7898   (1)|     20 |00:00:00.01 |    6269 |
|   2 |   COUNT                          |             |        |            |     20 |00:00:00.01 |    6269 |
|*  3 |    FILTER                        |             |        |            |     20 |00:00:00.01 |    6269 |
|   4 |     NESTED LOOPS                 |             |  10071 |  7898   (1)|  10000 |00:00:00.11 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|  10000 |00:00:00.08 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|  10000 |00:00:00.05 |      29 |
--------------------------------------------------------------------------------------------------------------

 

그렇다면  이런 경우에 어떻게 TOP 처리를 할수 있겠는가?
이 문제는 ROWNUM 에 대한 기본을 알면 해결된다.
TOP N건 처리시에 ROWNUM 과의 비교대상은 반드시 상수(혹은 변수) 이어야 한다. 
그렇지 않다면 TOP 처리는 실패한다.
아래의 SQL은 이 문제를 해결하였다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc)
where rownum <= (select page_count from new_dept where deptno = 20)  ;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |        |            |     20 |00:00:00.01 |      10 |
|   2 |   VIEW                          |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|     20 |00:00:00.01 |       3 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  9 |    INDEX UNIQUE SCAN            | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------


 PLAN 에서 보는것과 같이 서브쿼리를 사용하여 PAGE COUNT 를 상수화 하였다.
즉  rownum <= (서브쿼리) 형태가 되면 COUNT STOPKEY Operation 을 정상적으로 사용할수 있다.
"같은 테이블을 반복하여 읽지마라." 라는 튜닝의 기본적인 원칙이 깨지는 순간이다.
하지만 모든 규칙은 예외가 있음을 기억해야 한다.

결론 :
위에서 보는 바와 같이 COUNT STOPKEY 와 FILTER Operation 은 천당과 지옥의 차이이다.
몇천만건 혹은 몇억건 이상인 환경에서  FILTER  Operation 이 나온다면?
더이상 그 프로그램은 사용할수 없을것이다.
TOP SQL 처리시 주의사항은 ROWNUM 과 비교시 항상 상수(혹은변수)와 비교하거나 서브쿼리등을 이용하여 상수화 한후에 비교 하여야 한다는 것이다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. 2009.08.03 14:39  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

SQL 작성시 응용력을 필요로 하는경우가 많이 있다.
아래의 예제도 그런상황중의 하나이다.
일별매출실적 테이블에서 sysdate 기준으로 현재월의 첫번째 월요일부터 현재월의 마지막 금요일 까지의 실적을 구해야 되는 요구사항이 있다.
문제의 핵심은 해당월의  첫번째 월요일과 마지막 금요일을 구하는 것이다.
2009년 3월 기준으로 생각하면 첫번째 월요일은 2009년 3월 2일 마지막 금요일은 2009 년 3월 27일 이다. 

아래의 SQL 을 보자.

select  substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
  from 일별매출실적
 where 기준일자 between :현재월의 첫번째 월요일 and :현재월의 마지막 금요일
 group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)
;

위 SQL 은 변수를 2개 사용하고 있다.
:현재월의 첫번째 월요일:현재월의 마지막 금요일을 구하는 로직은 개발자가 자바 프로그램에서 작성할 예정이라고 한다.
그렇게 작성 할수는 있지만 로직이 길어지고  유지보수도 어려워지므로 Oracle DBMS 기능을 이용하여 쉽게 구하는 방법이 있는지 필자에게 물어보러 온 것이다.
아래의 SQL 을 보자

select next_day(trunc(sysdate,'mm')-1,'월요일') first_monday,
       trunc(next_day(last_day(sysdate)-7,'금요일')) last_monday
  from dual
;


FIRST_MONDAY LAST_MONDAY
------------ -----------
2009-03-02   2009-03-27

last_day, next_day 함수와 trunc 함수를 적절히 활용하면 문제를 쉽게 풀수 있다.
간단히 설명 하면 첫번째 월요일은 trunc(sysdate,'mm')-1 로 전달의 마지막 일자를 구하고 next_day 함수를 사용하여 다음 월요일을 구하면 된다.
마지막 금요일은 last_day 함수를 사용하여 해당월의 마지막 일자에서 7일을 뺀 결과를 next_day 함수를 사용하여 다음 금요일을 구하면 되는 것이다.

이렇게 하여 아래와 같은 simple 한 SQL 이 완성되었다.

select substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
  from 일별매출실적
 where 기준일자 between next_day(trunc(sysdate,'mm')-1,'월요일')
   and trunc(next_day(last_day(sysdate)-7,'금요일'))
 group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)  
;

결론:
문제의 핵심을 분석하고 DBMS 의 기능을 최대한 활용하여 상황을 빨리 해결 하는 능력이 필요하다.
문제해결 능력은 분석력 + 지식 + 응용력 이다.  
해당 SQL 의 경우 분석이나 지식보다는 응용력을 잘 발휘 해야되는 상황인 것 이다.

신고

'Oracle > SQL Pattern' 카테고리의 다른 글

SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://jongamk.tistory.com BlogIcon 핑구야 날자 2009.04.03 12:57 신고  댓글주소  수정/삭제  댓글쓰기

    같은 업종인것 같아 기븐이 좋네요
    프로젝트가 거의 마무리되어 조금은 홀가븐합니다.

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.04.05 13:48 신고  댓글주소  수정/삭제  댓글쓰기

    프로젝트가 잘마무리 되었다니 축하드립니다.
    핑구님 블로그엔 좋은글이 많네요.
    자주 참조 하겠습니다.

FPD(Filter Push Down)이란 뷰 바깥쪽의 조건을 뷰안으로 진입 시킨다는 의미이다.
FPD 는 자체로도 작동하지만 Subquery Flattening, View Merging, Join Predicate Pushdown 등이 진행될 때에도 부가적으로 실행되는 아주 중요한  Query Transformation 기법 이다.
FPD 와 관련하여 튜너및 DBA 들이 잘못알고 있는 사실이 있으므로 이것을 바로 잡고자 한다.
no_merge 힌트를 사용하면 뷰 바깥쪽의 조건들이 뷰 내로 파고들지 못한다는 미신이 있다.
과연 이것이 맞는 이야기인가?
먼저 테스트를 위하여 테이블을 하나 만들어야 한다.
환경 Oracle 11.1.0.6

CREATE TABLE scott.BIG_EMP AS    --> big_emp 생성
SELECT ROWNUM AS EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL + rownum as SAL, A.COMM, A.DEPTNO
  FROM scott.EMP A,
       (SELECT LEVEL AS NO FROM DUAL CONNECT BY LEVEL <= 2000) B;

ALTER TABLE scott.BIG_EMP --> PK 생성
   ADD ( CONSTRAINT PK_BIG_EMP PRIMARY KEY (EMPNO) USING INDEX );

CREATE INDEX scott.BIG_EMP_SAL_IX ON scott.BIG_EMP (SAL); --> SAL 항목에 인덱스 생성

dbms_stats.gather_table_stats('SCOTT', 'BIG_EMP', cascade => true);


 아래의 SQL 을 본다면 no_merge 힌트는 Filter Push Down(조건절을 뷰안으로 진입)을 방해하지 않음을 알수 있다.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ no_merge index(big_emp big_emp_sal_ix) */
                   deptno, max(SAL) max_sal
          from big_emp
         group by deptno)
where  max_sal  >  32900
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)    

재미있는 점은 뷰 바깥의 max_sal  >  32900 조건을 뷰내로 진입시키면서 아래처럼 where 절을 2단계로 추가 하였다.

1단계: having 절로 변환       --> having max(SAL) > 32900
2단계 :where 조건으로 변환  --> where sal > 32900

실제로 10053 Event 정보를 분석해보면 FPD(Filter Push Down) 가 2단계로 진행된다는 것을 알수 있다.
그러면 옵티마이져가 변경한 SQL 보자.

select /*+ gather_plan_statistics */
         deptno, max_sal
  from (select /*+ index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         where sal > 32900               --> 옵티마이져가 FPD 로 조건을 생성함.
         group by deptno
         having max(sal) >  32900    --> 옵티마이져가 FPD 로 조건을 생성함.
       )
;

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  VIEW                          |                |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   FILTER                       |                |      1 |      1 |00:00:00.01 |       3 |
|   3 |    HASH GROUP BY               |                |      1 |      1 |00:00:00.01 |       3 |
|   4 |     TABLE ACCESS BY INDEX ROWID| BIG_EMP        |      1 |      7 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | BIG_EMP_SAL_IX |      1 |      7 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(MAX("SAL")>32900)
   5 - access("SAL">32900)

Plan 및 Predicate Information 가 원본쿼리와 완전히 일치함을 알수 있다.

오히려 이런 경우는 원본쿼리에 merge 힌트를 사용하는것이 더 위험하다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics qb_name(outer) */
          deptno, max_sal
  from (select /*+ MERGE qb_name(inner) index(big_emp BIG_EMP_SAL_IX) */
                   deptno, max(sal) max_sal
          from big_emp
         group by deptno
         )
where  max_sal  >  32900
;


--------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------
|*  1 |  FILTER             |         |      1 |      1 |00:00:00.02 |     177 |
|   2 |   HASH GROUP BY     |         |      1 |      3 |00:00:00.02 |     177 |
|   3 |    TABLE ACCESS FULL| BIG_EMP |      1 |  28000 |00:00:00.03 |     177 |
--------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(MAX("SAL")>32900)

PLAN 정보의 Buffers(read 한 블럭수) 를 보면 일량이 3에서 177 로 늘어난 것을 알수 있다.
Group By 된 뷰가 merge 힌트에 의해서 해체 되었다.
Merge 되는 경우는 having 절만 생성되며 where 조건은 추가적으로 생성되지 않음을 알수 있다.
따라서 다음과 같은 SQL 로 변환된것을 알수 있다.

select deptno,
       max(sal) max_sal
  from big_emp
 group by deptno
 having max(sal) >  32900 ;  --> 추가적인 where 절이 없음

위와 같이 변경된것을 Complex View Merging 이라고 한다.
Complex 가 추가적으로 붙은 이유는 group by 절이 있는 뷰를 Merge(해체) 했기 때문이다.

결론 :
집계함수를 사용하는 뷰의 바깥에서 집계함수의 결과를 filter 할경우 merge 힌트를 사용하면 뷰가 해체 되면서 Having 절로 변환이 되지만 where 조건은 생성되지 않으므로 인덱스가 있다고 해도 사용할수 없음을 알수 있다.
오히려 이런 경우는 no_merge 힌트를 사용하여 FPD 를 유도하는 것이 정답인 것이다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2009.09.23 21:36 신고  댓글주소  수정/삭제  댓글쓰기

    지난내용이데 궁금해서 질문합니다.
    Subquery Flattening - SEMI join,
    View Merging - 뷰쿼리가 액세스 쿼리로 변환
    Join Predicate Pushdown - 액세스쿼리가 뷰쿼리로 변환
    맞습니까?
    filter push down 도 조건절진입과 동일한 것인가요?
    모 책에서는 조건절진입, 뷰 병합 으로만 설명이 되어있어 위의 내용들에 대한 정확한 개념이 잡히질 않네요..

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.23 22:42 신고  댓글주소  수정/삭제

      말씀하신 책을 읽은지가 오래되서 가물가물 합니다.
      그래서 다른방법으로 설명드립니다.
      Subquery Flattening - (SEMI/ANTI join, Subquery Unnesting) -서브쿼리가 없어지고 조인으로 바뀜.
      View Merging - 뷰 혹은 인라인뷰가 해체 됨(테이블끼리 조인으로 바뀜)
      JPPD - 조인조건이 뷰 혹은 인라인뷰 내부로 파고 듬
      FILTER PUSH DOWN - 조인조건이 아닌 FILTER 조건이 뷰 혹은 인라인뷰 내부로 파고듬.
      이렇게 정리하시면 쉬울겁니다.

      외국의 유명한 영문학 교수가 한국에 와서 영어문법에 대한 강연을 하는데 한 학생이 질문을 하였습니다.
      학생 :'5형식중에서 가장 쉬운것을 하나 소개해주세요'
      교수의 답변이 쇼킹 합니다.
      교수 : '5형식이 뭔가요?'

      뷰쿼리, 엑세스쿼리 ....음 용어 정말 어렵네요.
      하지만 일반적으로 통용되는 용어라면 아시는게 좋을수도....

  2. feelie 2009.09.24 12:59 신고  댓글주소  수정/삭제  댓글쓰기

    감사합니다.
    JPPD, FPD을 구별하기가 어려웠는데 조인조건이냐 아니냐 군요.
    감사합니다.

  3. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.12 15:14 신고  댓글주소  수정/삭제  댓글쓰기

    옵티마이져 진짜 영리하네요.

    좋은 글 감사드립니다~

  4. eqon 2013.11.20 11:34 신고  댓글주소  수정/삭제  댓글쓰기

    오라클 버전은 11.2.0.2.0

    view내부로 predicate파고 들어가지 않게 제어 하고 싶은데 계속 들어가버리네요.

    53 트레이스에서 확인해보니까 아래와 같이 FPD가 작동하네요.

    FPD: Considering simple filter push (pre rewrite) in query block QB2 (#0)

    FPD기능을 끌 수 있는 파라미터가 없을까요

    /*+ NO_MERGE no_push_pred
    OPT_PARAM('_push_join_predicate' 'false')
    OPT_PARAM('_pred_move_around' 'false')
    OPT_PARAM('_optimizer_push_pred_cost_based' 'false')
    OPT_PARAM('_simple_view_merging' 'false')
    OPT_PARAM('_optimizer_enhanced_filter_push' 'false' )
    OPT_PARAM('_optimizer_filter_pred_pullup' 'false') */

    이 정도 힌트 넣어봤는데 아무것도 안 먹네요.
    선생님 책 93페이지 filter push down절에서도 제어할 수 있는 파라미터는 안 나와 있네요. 할 수 없이 뷰 안에 의미없는
    rownum > 0 넣어주니까 기능이 작동하지 않는 걸 확인했는데요.
    힌트를 제어할 수 있는 방법을 알고 싶어요.

Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 Internal Hint Transformation 개념을 설명한적이 있다.
오늘은 예제 몇가지에 대하여 소개하려 한다.
먼저  Internal Hint Transformation 이 왜 일어나는지 설명하기 위하여 힌트의 종류를 나누어 보자.
오라클 Performace Tuning Guide 에 보면 힌트의 Type 에 대해서 아래와 같이 분류하고 있다.

1.Single Table Hints : 하나의 테이블이나 인덱스에 대하여 사용하는 힌트
                               index 관련 힌트나 use_nl, use_merge, use_hash 등이 여기 속한다.
2.Multi Table Hints : 여러 테이블이나 블럭에 대하여 사용하는 힌트.
                             leading 힌트나 index_join, index_combine 등이 여기에 해당된다.
3.Query Block Hints: 하나의 쿼리블럭에 사용하는 힌트
                             STAR_TRANSFORMATION, UNNEST, MERGE, PUSH_PRED,  USE_CONCAT, NO_EXPAND 등이 여기에 해당된다.
4.Statement Hints : 전체 SQL 단위로 사용하는 힌트
                           all_rows, first_rows_n 등이 여기에 해당된다.

가장 흔한 힌트변환은 use_nl(a, b) 를 leading(a b) use_nl(b) 로 바꾸는 것이다.
use_nl 은 Single Table Hints 이기 때문이다.
또한 use_nl_with_index 등을 사용하여도 각각 index, use_nl 힌트등으로 바꾸어 버린다.
이중에서 가장 극적인 Internal Hint Transformation 예제는 Index_Combine 이다.
아래 예제를 보자.

환경 :10.2.0.4

먼저 Bit map 인덱스를 2개 만든다.
create bitmap index IX_EMP_N2 on emp(mgr);
create bitmap index IX_EMP_N3 on emp(deptno);

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

 select /*+ gather_plan_statistics index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */
        empno, mgr, deptno
   from emp
  where NOT( mgr = 7698 )
    and  deptno = 20;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'))

오라클은 위의 힌트를 어떻게 바꿀까?
dbms_xplan.display_cursor 의 결과중에 Outline Data 를 보면 아래와 같다.
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      OPT_PARAM('_bloom_filter_enabled' 'false')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'false')
      OPT_PARAM('optimizer_index_cost_adj' 25)
      OPT_PARAM('optimizer_index_caching' 90)
      FIRST_ROWS(1)
      OUTLINE_LEAF(@"SEL$1")
      BITMAP_TREE(@"SEL$1"
"EMP"@"SEL$1
" AND(("EMP"."DEPTNO") MINUS(("EMP"."MGR")) MINUS_NULL(("EMP"."MGR"))))
      END_OUTLINE_DATA
  */


이상하지 않은가?
Bitmap_tree 라는 힌트는 사용하지도 않았다.
오라클은 Index_Combine 힌트를 Bitmap_Tree 힌트로 바꾼것이다.
이 암호와도 같은 힌트를 간단히 바꾸면 아래와 같다.
BITMAP_TREE(emp and( (emp.deptno) minus((emp.mgr)) minus_null((emp.mgr))))
간단히 설명하면  deptno = 20 을 만족하는 집합에서  mgr = 7698  을 만족하는 집합을 뺴주는것(minus) 이다.
그렇다면 남은 minus_null 힌트는 무엇일까?
이힌트는 부정형으로 Bit Map 비교시에만 나타난다.
다시말하면 bit map 연산시 mgr 이 7698 이 아닌것을 나타내면 mgr is null 인 데이터가 포함이 되어 버린다.
따라서 mgr is null 인 집합도 빠져야 하기 때문에 옵티마이져는 minus_null(mgr) 힌트를 사용한 것이다.
이것은 Bit map Operation 의 특성에서 나온것이다.
참고로 where 절에 mgr is not null 이라고 명시하거나 혹은 not null Constraints 를 주게되면 minus_null 힌트는 사라진다.

아래의 Predicate Information 를 보면 상세히 알수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("DEPTNO"=20)            --> (("EMP"."DEPTNO")
   6 - access("MGR"=7698)              --> MINUS(("EMP"."MGR"))
   7 - access("MGR" IS NULL)         --> MINUS_NULL(("EMP"."MGR"))

결론 : Query Transfomation 에는 Internal Hint Transformation 도 포함 되어야 한다.
         Internal Hint Transformation 는 힌트의 용법을 정확히 지키지 않으면 거의 모든곳에서 나타날수 있다.
         또한 용법을 정확히 사용하여도 내부적으로 변환시키는 경우가 많이 있다.
         그러나 이런것이 나타난다고 해서 걱정할 필요는 없다.
         또한 Internal 힌트를 사용할 필요도 없다.
         단지 "옵티마이져가 내부적으로 이런일을 하고 있다." 라고 알고 있으면 당황하지 않을 것이다.


신고
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

댓글을 달아 주세요

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

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