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
,

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
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Posted by extremedb
,

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