[SQLP] 쿼리변환-2 (조건절 Pushing, 조건절 이행)

4. 조건절 Pushing

• 옵티마이저가 뷰를 처리함에 있어 1차적으로 뷰 Merging을 고려하지만, 조건절 Pushing을 시도할 수도 있다.
• 조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리량 최소 및 리턴 결과 감소로 다음 단계에서 처리를 줄일 수 있다.
• 크게 3가지 조건절 Pushing이 있다.
  1. 조건절 Pushdown: 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안 쪽으로 밀어 넣는 것을 말함
  2. 조건절 Pullup: 쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내와서 다른 쿼리 블록으로 Pushdown 하는데 사용
  3. 조인 조건 Pushdown: NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어넣는 것을 말한다.

가. 조건절(Predicate) Pushdown

• 아래 쿼리 처리 인라인 뷰를 group by로 만들 때, 미리 필터링 할 수 있고 그것이 인덱스 스캔이면 효과적일 수 있다.

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

• 실행계획을 보면 인라인 뷰 내에서 조건이 없음에도 불구하고 조건절 Pushing이 발생하여 Index Range Scan이 발생했다.

-----------------------------------------------------------
| Id | Operation                       | Name            | Rows | Bytes |
-----------------------------------------------------------
| 0  | SELECT STATEMENT                |                 | 1    | 26    |
| 1  |   VIEW                            |                 | 1    | 26    |
| 2  |    SORT GROUP BY NOSORT            |                 | 1    | 7     |
| 3  |    TABLE ACCESS BY INDEX ROWID     | EMP             | 6    | 42    |
|* 4 |     INDEX RANGE SCAN                | EMP_DEPTNO_IDX  | 6    |       |
-----------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------
4 - access("DEPTNO"=30)

• 아래 같은 경우도 보면 인라인 뷰 a에는 조건이 없고 나중에 dept 테이블과 나중에 조인하는 쿼리이다.

select 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

• 조건이 없음에도 불구하고 인라뷰 내부의 emp 테이블에서 deptno을 통해 Index Range Scan이 발생했다.

| Id | Operation                   | Name           | Rows | Bytes | 
|----|-----------------------------|----------------|------|-------|
| 0  | SELECT STATEMENT            |                | 1    | 39    |
| 1  |   NESTED LOOPS                |                | 1    | 39    |
| 2  |    TABLE ACCESS BY INDEX ROWID | DEPT           | 1    | 13    |
| 3  |     INDEX UNIQUE SCAN           | DEPT_PK        | 1    |       |
| 4  |    VIEW                        |                | 1    | 26    |
| 5  |     SORT GROUP BY               |                | 1    | 7     |
| 6  |      TABLE ACCESS BY INDEX ROWID | EMP            | 6    | 42    |
| 7  |       INDEX RANGE SCAN            | EMP_DEPTNO_IDX | 6    |       |

Predicate Information (identified by operation id):
3 - access("B"."DEPTNO"=30)
7 - access("DEPTNO"=30)

나. 조건절 PullUp

• 조건절 PullUp은 다른 뷰 쿼리 블록에서 올려 다른 쿼리 블록으로 내리는 행위이다.
• e2 인라인 뷰에는 조건절 없이 group by가 작성되어있다.

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

• 아래의 실행계획을 보면 e2에도 Index Range Scan 이 동작하였다.

------------------------------------------------------------
| Id | Operation                     | Name            | Rows | Bytes |
------------------------------------------------------------
|  0 | SELECT STATEMENT              |                 |    1 |    65 |
|* 1 |   HASH JOIN                     |                 |    1 |    65 |
|  2 |    VIEW                          |                 |    1 |    26 |
|  3 |     HASH GROUP BY                 |                 |    1 |     5 |
|  4 |      TABLE ACCESS BY INDEX ROWID   | EMP             |    5 |    25 |
|* 5 |       INDEX RANGE SCAN              | EMP_DEPTNO_IDX  |    5 |       |
|  6 |   VIEW                          |                 |    1 |    39 |
|  7 |    HASH GROUP BY                 |                 |    1 |     5 |
|  8 |     TABLE ACCESS BY INDEX ROWID   | EMP             |    5 |    25 |
|* 9 |      INDEX RANGE SCAN              | EMP_DEPTNO_IDX  |    5 |       |
------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------------------------
1 - access("E1"."DEPTNO"="E2"."DEPTNO")
5 - access("DEPTNO"=10)
9 - access("DEPTNO"=10)

• 아래와 같이 쿼리가 변환되었다고 생각하면 된다.

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

다. 조인 조건 Pushdown

• 조인 조건 Pushdown은 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서, NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner 쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다.
• 아래 쿼리를 보면 emp가 포함한 인라인 뷰에는 조건 없이 group by 가 작성되어있다.

select 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

• 하지만 VIEW PUSHED PREDICATE 의 실행계획이 명시되어있고 deptno 를 통해 emp에서 Index Range Scan이 발생하고 있다.

-----------------------------------------------------------
| Id  | Operation                 | Name           | Rows | Bytes | 
-----------------------------------------------------------
|  0  | SELECT STATEMENT          |                |    4 |   116 | 
|  1  |  NESTED LOOPS OUTER        |                |    4 |   116 | 
|  2  |   TABLE ACCESS FULL         | DEPT           |    4 |    64 | 
|  3  |    VIEW PUSHED PREDICATE     |                |    1 |    13 | 
|* 4  |    FILTER                    |                |      |       | 
|  5  |     SORT AGGREGATE            |                |    1 |     7 | 
|  6  |      TABLE ACCESS BY INDEX ROWID| EMP           |    5 |    35 | 
|* 7  |       INDEX RANGE SCAN          | EMP_DEPTNO_IDX |    5 |       | 
-----------------------------------------------------------

Predicate Information (identified by operation id):
-----------------------------------------------------------
4 - filter(COUNT(*)>0)
7 - access("DEPTNO"="D"."DEPTNO")

• 그런데 group by 가 여러개 일 경우 반복적으로 접근한다고 하는데 이때도 비효율이 발생한다.
• 아래 쿼리가 그 예시이다.
• Oracle 11g에서는 옵티마이저가 이 마저도 최적화 시키니 그 전 버전에서만 신경쓰면 될 것 같다.

select 
    d.deptno
    , d.dname 
    ,(select avg(sal) from emp where deptno = d.deptno) avg_sal 
    ,(select min(sal) from emp where deptno = d.deptno) min_sal 
    ,(select max(sal) from emp where deptno = d.deptno) max_sal 
from dept d

• 아래와 같이 조금 복잡하지만 접근을 최소화 시킬 수 있다.
  • 아이디어만 참고하자

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 /*+ no_merge */ 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 
)

5. 조건절 이행

• 쉽게 말해 A=B이고 B=C이면 A=C의 삼단논법식 추론 논리이다.
• 아래 쿼리들 dept 테이블에는 deptno = 10 조건은 없다.

select * 
from dept d, emp e 
where e.job = 'MANAGER' 
and e.deptno = 10 
and d.deptno = e.deptno

• 하지만 실행계획을 보면 dept 테이블에도 추가되었다.

-------------------------------------------------------------
| Id  | Operation                  | Name     | Rows | Bytes | Cost (%CPU) |
-------------------------------------------------------------
|  0  | SELECT STATEMENT           |          |    1 |    57 | 2 (0)       |
|  1  |   NESTED LOOPS               |          |    1 |    57 | 2 (0)       |
|  2  |    TABLE ACCESS BY INDEX ROWID| DEPT     |    1 |    20 | 1 (0)       |
|* 3  |     INDEX UNIQUE SCAN          | DEPT_PK  |    1 |       | 0 (0)       |
|  4  |   TABLE ACCESS BY INDEX ROWID| EMP      |    1 |    37 | 1 (0)       |
|* 5  |    INDEX RANGE SCAN           | EMP_IDX  |    1 |       | 0 (0)       |
-------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

• 아래와 같이 추론되서 변환되었다고 생각하면 된다.

select * 
from dept d, emp e 
where e.job = 'MANAGER' 
and e.deptno = 10
and d.deptno = 10