4. 조건절 Pushing

조회 수 12097 추천 수 0 2013.08.21 12:33:51
오예스 *.182.62.197
04. 조건절 pushing
- 뷰를 액세스하는 쿼리를 최적활 할때 옵티마이져는 1차적으로 뷰 Merging을 고려한다. 하지만 아래와 같은 이유로 뷰 Merging에 실패할수 있다.
  1) 복합 뷰(Complex View)Merging 기능이 활성화
  2) 사용자가 no_merge 힌트사용
  3) Non-mergeable Views : 뷰 Merging 시행하면 부정확한 결과 가능성
  4) 비용기반 쿼리 변환이 작동해 NO Merging 선택(10g 이후)

조건절 pushing 장점 : 뷰 안에서의 처리 일량을 최소화(group by 등 연산 작업 최소화, 인덱스 액세스 조건으로 사용) 하게 됨은 물론 리턴되는 결과 건수를 줄임으로 써 다음 단계에서 처리해야 할 일량을 줄일수 있다.

- 조건절 Pushing 종류
  1) 조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함.
  2) 조건절(Predicate) Pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown하는데 사용함.
  3) 조인 조건(Join Predicate) Pushdown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값은 조건인 Inner 쪽(=right side) 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함.

- 관련 힌트와 파라미터
 조건절 Pushdown과 Pulup은 항상 더 나은 성능을 보장함으로 별도의 힌트를 제공하지 않음.
  하지만 조인 조건(Join Predicate) 은 성능이 나빠질 수 도 있기때문에, 제어할수 있도록 push_pred와 no_push_pred 힌트 제공.

- Non-pushable view, Non-mergable View
 rownum, 분석함수(Analytic Function) 사용시 발생. rownum 및 분석함수는 집합을 출력하는 단계에서 실시간 부여되는 값인데, Pushing이 작동하면 기존에 없던 조건절이 생겨 같은 로우가 다른 값을 부여받을수 있기 때문.

  1) 조건절(Predicate) Pushdown
      - GROUP BY 절을 포함한 뷰에 대한 조건절 Pushdown
아래 쿼리는 group by 절을 포함한 복합 뷰 사례인데, 9i 이후로는 복합 뷰 Merging이 기본적으로 활성화되므로  뷰 Merging이 발생할 것이다.
이를 막도록 _complex_view_merging 파라미터를 false로 바꾸거나 no_merge 힌트를 사용할 대의 실행계획을 확인해 본다.

create table dept as select * from scott.dept;
create table emp as select * from scott.emp;
alter table dept add constraint dept_pk primary key(deptno);
create index emp_deptno_idx on emp(deptno);
exec dbms_stats.gather_table_stats(user, 'emp');
exec dbms_stats.gather_table_stats(user, 'dept');

alter session set "_complex_view_merging"= false;

select deptno, avg_sal
from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
where  deptno = 30;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
  1    0   SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=7)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  3    2       INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)

select /*+ no_merge(a) */
      b.deptno, b.dname, a.avg_sal
from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
    , dept b
where  a.deptno = b.deptno            
and    b.deptno = 30;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
  1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=28)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
  3    2       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  4    1     VIEW (Cost=2 Card=1 Bytes=15)
  5    4       SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
  6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  7    6           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)



select /*+ no_merge(a) */
      b.deptno, b.dname, a.avg_sal
from  (select deptno, avg(sal) avg_sal from emp group by deptno) a
    , dept b
where  a.deptno = b.deptno            
and    b.deptno = 30                  
and    a.deptno = 30;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=28)
  1    0   NESTED LOOPS (Cost=3 Card=1 Bytes=28)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
  3    2       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  4    1     VIEW (Cost=2 Card=1 Bytes=15)
  5    4       SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
  6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  7    6           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)

        
  - union 집합 연산자를 포함한 뷰에 대한 조건절 Pushdown
    union 집합 연산자를 포함한 뷰는 Non-meragble View에 속하므로 복합 뷰(Complex view) Merging 기능을 활성화하더라도 뷰 Merging에 실패한다. 따라서 조건절 Pushing을 통해서만 최적화가 가능하며, 아래는 그 사례를 보이고 있다.

select *
from  (select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate
      from emp
      where  job = 'CLERK'
      union all
      select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate
      from emp
      where  job = 'SALESMAN' ) v
where  v.deptno = 30;
Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=148)
  1    0   VIEW (Cost=4 Card=2 Bytes=148)
  2    1     UNION-ALL
  3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=33)
  4    3         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  5    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=33)
  6    5         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)



select /*+ ordered use_nl(e) */ d.dname, e.*
from   dept d
    ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
      where  job = 'CLERK'
      union all
      select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
      where  job = 'SALESMAN' ) e
where  e.deptno = d.deptno
and    d.deptno = 30;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=2 Bytes=174)
  1    0   NESTED LOOPS (Cost=5 Card=2 Bytes=174)
  2    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13)
  3    2       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  4    1     VIEW (Cost=4 Card=2 Bytes=148)
  5    4       UNION-ALL
  6    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=33)
  7    6           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  8    5         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=33)
  9    8           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)




  2) 조건절(Predicate) Pullup
    쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown하는데 사용함(-> Predicate Move Around).
select * from
 (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp group by deptno) e2
where  e1.deptno = e2.deptno ;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=65)
  1    0   HASH JOIN (Cost=5 Card=1 Bytes=65)
  2    1     VIEW (Cost=2 Card=1 Bytes=26)
  3    2       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  5    4           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  6    1     VIEW (Cost=2 Card=1 Bytes=39)
  7    6       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  8    7         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  9    8           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)

select * from
 (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) from emp where deptno = 10 group by deptno) e2
where  e1.deptno = e2.deptno ;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=65)
  1    0   HASH JOIN (Cost=5 Card=1 Bytes=65)
  2    1     VIEW (Cost=2 Card=1 Bytes=26)
  3    2       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  5    4           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  6    1     VIEW (Cost=2 Card=1 Bytes=39)
  7    6       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  8    7         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  9    8           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)

select /*+ opt_param('_pred_move_around', 'false') */ * from
 (select deptno, avg(sal) from emp where deptno = 10 group by deptno) e1
,(select deptno, min(sal), max(sal) avg_sal from emp group by deptno) e2
where  e1.deptno = e2.deptno;

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1 Bytes=65)
  1    0   HASH JOIN (Cost=7 Card=1 Bytes=65)
  2    1     VIEW (Cost=2 Card=1 Bytes=26)
  3    2       HASH (GROUP BY) (Cost=2 Card=1 Bytes=7)
  4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=35)
  5    4           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  6    1     VIEW (Cost=4 Card=3 Bytes=117)
  7    6       HASH (GROUP BY) (Cost=4 Card=3 Bytes=21)
  8    7         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=98)


   3) 조인 조건(Join Predicate) Pushdown
       NL 조인 수행 중에 드라이빙 테이블에서 읽은 값은 조건인 Inner 쪽(=right side) 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함
      조건절(Predicate) Pushdown의 일종이지만 차이점은, 지금까지 보았던 조인문에서의 조건절 Pushdown은 상수 조건이 조인 조건을 타고 전이된 것을 Pusing하는 기능이었던 반면, 지금 설명하는 조인 조건 Pushdown은 조인을 수행하는 중에 드라이빙 집합에서 얻은 값을 뷰 쿼리 블록 안에 실시간으로 Pushing 하는 기능이다.

select /*+ no_merge(e) push_pred(e) */ *
from  dept d, (select empno, ename, deptno from emp) e
where e.deptno(+) = d.deptno
and   d.loc = 'CHICAGO';

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=4 Bytes=220)
  1    0   NESTED LOOPS (OUTER) (Cost=5 Card=4 Bytes=220)
  2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
  3    1     VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=35)
  4    3       TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=5 Bytes=80)
  5    4         INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)



       push_pred : 조인 조건 Pushdown을 유도한다.
       no_push_pred : 조인 조건 Pushdown을 방지한다.
     
      group by 절을 포함한 뷰에 대한 조인 조건 Pushdown.
      group by를 포함하는 부에 대한 조인 조건 Pushdown 기능은 11g에 와서야 제공.
-- 10g
select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */
      d.deptno, d.dname, e.avg_sal
from   dept d
    , (select deptno, avg(sal) avg_sal from emp group by deptno) e
where  e.deptno(+) = d.deptno;

-- 11g
select /*+ leading(d) use_nl(e) no_merge(e) push_pred(e) index(e (deptno)) */
      d.deptno, d.dname, e.avg_sal
from   dept d
    , (select deptno, avg(sal) avg_sal from emp group by deptno) e
where  e.deptno(+) = d.deptno;

select d.deptno, d.dname
    ,(select avg(sal) from emp where deptno = d.deptno)
from   dept d;


-- 10g 이하 집계함수가 두 개 이상일 때 활용법
select deptno, dname
    , to_number(substr(sal, 1, 7)) avg_sal
    , to_number(substr(sal, 8, 7)) min_sal
    , to_number(substr(sal, 15))   max_sal
from (
 select d.deptno, d.dname
      ,(select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal)
        from emp where deptno = d.deptno) sal
 from   dept d
);

create or replace type sal_type as object
( avg_sal number, min_sal number, max_sal number )
/
select deptno, dname
    , a.sal.avg_sal, a.sal.min_sal, a.sal.max_sal
from (
 select d.deptno, d.dname
      ,(select sal_type( avg(sal), min(sal), max(sal) )
        from emp where deptno = d.deptno) sal
 from   dept d
) a;



Union 집합 연산을 포함한 뷰에 대한 조인 조건  Pushdown
select /*+ push_pred(e) */ d.dname, e.*
from   dept d
    ,(select deptno, empno, ename, job, sal, sal * 1.1 sal2, hiredate from emp
      where  job = 'CLERK'
      union all
      select deptno, empno, ename, job, sal, sal * 1.2 sal2, hiredate from emp
      where  job = 'SALESMAN' ) e
where  e.deptno = d.deptno
and    d.loc = 'CHICAGO';

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=2 Bytes=200)
  1    0   NESTED LOOPS (Cost=7 Card=2 Bytes=200)
  2    1     TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=20)
  3    1     VIEW (Cost=4 Card=1 Bytes=80)
  4    3       UNION ALL PUSHED PREDICATE
  5    4         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=36)
  6    5           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)
  7    4         TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=1 Bytes=36)
  8    7           INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX) (Cost=1 Card=5)



Outer 조인 뷰에 대한 조인 조건 Pushdown
Outer 조인에서 Inner 쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이져는 다음 2가지 방법중 하나를 선택.
1. 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 Merging을 시도한다.
2. 뷰 내에서 참조하는 테이블이 두 개 이상일 때, 조인 조건식을 뷰 안쪽으로 Pushing 하려고 시도한다.
select /*+ push_pred(b) */
      a.empno, a.ename, a.sal, a.hiredate, b.deptno, b.dname, b.loc, a.job
from   emp a
    ,(select e.empno, d.deptno, d.dname, d.loc
      from   emp e, dept d
      where  d.deptno = e.deptno
      and    e.sal >= 1000
      and    d.loc in ( 'CHICAGO', 'NEW YORK' ) ) b
where  b.empno(+) = a.empno
and    a.hiredate >= to_date('19810901', 'yyyymmdd');

Execution Plan
--------------------------------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=14 Bytes=952)
  1    0   NESTED LOOPS (OUTER) (Cost=59 Card=14 Bytes=952)
  2    1     TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=476)
  3    1     VIEW PUSHED PREDICATE (Cost=4 Card=1 Bytes=34)
  4    3       NESTED LOOPS (Cost=4 Card=1 Bytes=35)
  5    4         TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=15)
  6    4         TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
  7    6           INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)