Chap04. 04 조건절 Pushing

• 뷰를 액세스하는 쿼리를 최적화할 때 옵티마이저는 1차적으로 뷰 Merging을 고려합니다.
• 하지만, 아래와 같은 이유로 뷰 Merging이 실패할 수 있는데요.
  1. 복합 뷰(Complex View) Merging 기능이 비활성화
  2. 사용자가 NO_MERGE 힌트 사용
  3. Non-mergeable Views : 뷰 Merging 시행하면 부정확한 결과 가능성
  4. 비용기반 쿼리 변환이 작동해 No Merging 선택(10g 이후)

 

• 어떤 이유에서건 뷰 Merging에 실패했을 때, 옵티마이저는 포기하지 않고 2차적으로 조건절 Pushing을 시도합니다.
• 이는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능을 일컫습니다.

 

 

<조건절 Pushing> : 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing 하는 기능

• 조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화(Group by 등 연산 작업 최소화, 인덱스 액세스 조건으로 사용) 하게 됨은 물론 리턴되는 결과 건수를 줄임으로 써 다음 단계에서 처리해야할 일량을 줄 일 수 있습니다.
• 종류
  1. 조건절 Pushdown : 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
  2. 조건절 Pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown 하는데 사용함(=Predicate Move Around)
  3. 조인 조건 Pushdown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함

 

<조건절 Pushing 관련 파라미터>

• 조건절 Pushdown과 Pullup은 항상 더 나은 성능을 보장하므로 별도의 힌트를 제공하지 않습니다. 하지만 조인 조건 Pushdown은 NL 조인을 전제로 하기 때문에 성능이 더 나빠질 수 있습니다.
• 따라서 오라클은 조인 조건 Pushdown을 제어할 수 있도록 아래의 힌트를 제공합니다.
  • [ PUSH_PRED ] : 조인 조건 Pushdown을 유도한다.
  • [ NO_PUSH_PRED ] : 조인 조건 Pushdown을 방지한다.

참고만..

• 그런데 조인 조건 Pushdown 기능이 10g에 와서는 비용기반 쿼리 변환으로 바뀌었고, 9i에서 빠르게 수행되던 쿼리가 10g로 이행하면서 오히려 느려지는 현상이 종종 나타나곤 했는데, 이때는 문제가 되는 쿼리 레벨에서 아래와 같이 힌트를 이용해 파라미터를 false로 변경하면 됩니다.

SELECT /*+ OPT_PARAM('_OPTIMIZER_PUSH_PRED_COST_BASED', 'FALSE') */ * FROM ....

 

 

 

<Non-pushable View>

• 뷰 안에 Rownum 사용하면 Non-mergeable View가 되는데, 동시에 Non-Pushable View가 된다는 사실도 기억하길 바랍니다. 왜냐하면, rownum은 집합을 출력하는 단계에서 실시간 부여되는 값인데, 조건절 Pushing이 작동하면 기존에 없던 조건절이 생겨 같은 로우가 다른 값을 부여받을 수 있기 때문입니다.

 

01. 조건절 Pushdown

---

<Group by 절을 포함한 뷰에 대한 조건절 Pushdown>

• Group by 절을 포함한 복합 뷰(Complex View) Merging에 실패했을 때, 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽에 밀어 넣을 수 있다면 Group by 해야 할 데이터량을 줄일 수 있습니다.

 

예시

ALTER SESSION SET "_COMPLEX_VIEW_MERGING" = FALSE; --- View Merging 발생 안하도록

SELECT DEPTNO, AVG_SAL
FROM (SELECT DEPTNO, AVG(SAL) AVG_SAL FROM EMP GROUP BY DEPTNO) A
WHERE DEPTNO = 30;

실행계획

 

• 뷰 Merging에 실패했지만 옵티마이저가 조건절을 뷰 안쪽으로 밀어 넣음(pushdown)으로써, emp_deptno_idx 인덱스를 사용한 것을 볼 수 있습니다.
  • Predicate Information 정보에서 4 - Access("DEPTNO"=30) 나타남과 EMP_X01 Index를 사용한 것을 볼 수 있습니다.
• 조건절 Pushdown이 작동 안 했다면 emp 테이블을 Table Full Scan 하고서 Group by 이후에 deptno = 30 조건을 필터링했을 것입니다.

 

Join문 Test

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;

실행계획

• 이것은 뒤에서 설명하는 '조인 조건 Pushdown'과 구분되어야 하는데, 
• NL 조인을 수행하는 동안 dept 테이블에서 읽은 조인 컬럼(deptno) 값을 건건이 뷰 쿼리 블록에 pushdown 한 것이 아니기 때문입니다.
• 여기서는 인라인 뷰 자체적으로 사전에 deptno = 30 조건절을 적용해 데이터량을 줄이고, group by를 하고 나서 조인에 참여하였습니다.
• deptno = 30 조건이 인라인 뷰에 pushdown 될 수 있었던 이유는, 조건절 이행이 먼저 일어났기 때문입니다.
  • b.deptno = 30 조건이 조인 조건을 타고 a쪽에 전이됨으로써 아래와 같이 a.deptno = 30 조건절이 내부적으로 생성된 것입니다.

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;

• 이상태에서 A.DEPTNO = 30 조건절이 인라인 뷰 안쪽으로 Pushdown이 된 것이므로 일반적인 조건절 Pushing으로 이해해야 합니다.

 

 

<Union 집합 연산자를 포함한 뷰에 대한 조건절 Pushdown>

• Union 집합 연산자를 포함한 뷰는 Non-mergeable View에 속하므로 복합 뷰 Merging 기능을 활성화하더라도 뷰 Merging에 실패합니다. 따라서 조건절 Pushing을 통해서만 최적화가 가능하며, 아래보도록 하겠습니다.

CREATE INDEX EMP_X01 ON EMP(DEPTNO, JOB);
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;

실행계획

 

• EMP_X01 인덱스는 [DEPTNO + JOB] 순으로 구성된 결합 인덱스고, 인덱스 선두컬럼인 deptno 조건이 뷰 쿼리 블록 안쪽에 기술되지 않았음에도 이 인덱스가 정상적으로 Range Scan을 보이고 있습니다.
• 조건절 Pushing이 일어났기 때문이며, 실행계획 아래쪽 Predicate Information내에 deptno 조건이 인덱스 액세스 조건으로 사용되었음을 알 수 있었습니다.

 

아래는 조인 조건을 타고 전이된 상수 조건이 뷰 쿼리 블록에 Pushing된 경우입니다.

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

 

 

 

 

02. 조건절 Pullup

---

• 조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내기도 하는데, 이를 "조건절 Pullup" 이라고 합니다.
• 그리고 그것을 다시 다른 쿼리 블록에 Pushdown하는 데 사용합니다.

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에는 deptno = 10 조건이 없지만 Predicate 정보를 보면 양쪽 모두 이 조건이 EMP_X01(emp_deptno_idx) 인덱스의 액세스 조건으로 사용된 것을 볼 수 있습니다. 아래와 같은 형태로 쿼리 변환이 일어난 것입니다.

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;

 

 

 

아래는 Predicate Move Around 기능이 작동하지 않았을 때 어떤 비효율이 발생하는지를 보여주는데요.

opt_param 힌트를 이용해 이 기능을 비활성화시켰고, 이 때문에 Index Full Scan이 나타나게 됩니다.

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

 

 

 

03. 조인 조건 Pushdown

---

• 조인 조건 Pushdown은 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 것으로서, NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner 쪽 뷰 쿼리 블록내에서 참조할 수 있도록 하는 기능입니다.
• 조인을 수행하는 중에 드라이빙 집합에서 얻은 값을 뷰 쿼리 블록 안에 실시간으로 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';

 

실행계획

• 인라인 뷰 내에서 메인 쿼리에 있는 D.DEPTNO 컬럼을 참조할 수 없음에도 옵티마이저가 이를 참조하는 조인 조건을 뷰 안쪽에 생성해 준것을 알 수 있습니다.
• 조인 조건 Pushdown이 일어난 것이며, 실행계획상에 'VIEW PUSHED PREDICATE' 오퍼레이션이 나타난 것을 통해서도 알 수 있습니다.

 

조인 조건 Pushdown 제어 힌트

1. [ PUSH_PRED ] : 조인 조건 Pushdown을 유도한다.
2. [ NO_PUSH_PRED ] : 조인 조건 Pushdown을 방지한다.

 

이를 제어하는 파라미터 3가지

• _PUSH_JOIN_PREDICATE : 뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown 하는 기능을 활성화합니다. union 또는 union all을 포함하는 Non-mergeable 뷰에 대해서는 아래 두 파라미터가 따로 제공됩니다.
• _PUSH_JOIN_UNION_VIEW : union all을 포함하는 Non-Mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화합니다.
• _PUSH_JOIN_UNION_VIEW2 : union을 포함하는 Non-Mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화합니다.

 

 

<Group by 절을 포함한 뷰에 대한 조인 조건 Pushdown>

• Group by를 포함하는 뷰에 대한 조인 조건 Pushdown 기능은 11g부터 제공

SELECT /*+ LEADING(D) USE_NL(E) NO_MERGE(E) PUSH_PRED(E) */
       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;

10g - 실행계획

• 조인 조건 Pushdown이 작동하지 않아 emp쪽 인덱스를 Full Scan한다.
• dept 테이블에서 읽히는 DEPTNO마다 EMP 테이블 전체를 Group by 하고 있다.

 

 

11g에서 확인한 실행계획

11g - 실행계획

• "VIEW PUSHED PREDICATE" Operation이 나타나며,
• EMP_X01(emp_deptno_idx) Index를 통해 EMP 테이블을 액세스합니다.
  • DEPT 테이블로부터 넘겨진 DEPTNO에 대해서만 Group by 하는 것입니다.
  • 이 기능은 부분범위처리가 필요한 상황에서 특히 유용

 

 

10g 이하의 버전에서는 스칼라 서브쿼리로 쉽게 변환하여 처리

SELECT D.DEPTNO, D.DNAME
      ,(SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM DEPT D;

실행계획

• 만약에 집계함수가 여러개일 경우에는 그만큼 서브쿼리를 늘릴 시 그만큼 EMP 테이블을 방문해야하니 성능 저하 우려되며
• 그렇다면 집계함수가 여러개일 경우는?
  1. 필요한 컬럼 값을 모두 결합 후 바깥쪽 액세스 쿼리에서 substr 함수로 분리
  2. Object TYPE의 사용

 

 

<UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown>

CREATE IDNEX DEPT_IDX ON DEPT(LOC);
CREATE INDEX EMP_X01 ON EMP(DEPTNO, JOB);


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';

실행계획

 

• EMP_X01 인덱스는 [DEPTNO + JOB] 순으로 구성된 결합 인덱스고, 인덱스 선두컬럼인 DEPTNO 조건이 뷰 쿼리 블록 안에 기술되지 않았음에도, 이 인덱스가 정상적인 Range Scan 하는 점을 보입니다.
• 실행계획 상에 UNION ALL PUSHED PREDICATE가 눈에 띕니다.
  • ⓐ DEPT_IDX [LOC]를 통해 LOC='CHICAGO' 조건에 해당하는
  • ⓑ DEPT Table을 Scan하면서 얻은 DEPTNO 값을
  • ⓒ 뷰 쿼리 블록안에 제공하면서 맞는 부분에 대해서만 조인을 수행합니다.
• 버전이 낮은 Oracle DBMS에서는 USE_NL과 PUSH_PRED 힌트를 같이 사용 시 조인조건 PUSHDOWN이 작동 안 할 수 있기에 PUSH_PRED Hint만 사용하길 권고합니다. PUSH_PRED 자체가 NL로 수행하기에 굳이 USE_NL Hint를 사용할 필요가 없습니다.

 

<OUTER 조인 뷰에 대한 조인 조건 Pushdown>

• Outer 조인에서 Inner 쪽 집합이 뷰 쿼리 블록일 때, 뷰 안에서 참조하는 테이블 개수에 따라 옵티마이저는 2가지 방법 중 하나를 선택합니다.
  1. 뷰 안에서 참조하는 테이블이 단 하나일 때, 뷰 Merging을 시도합니다.
     • 만약 NO_MERGE Hint를 사용해 뷰 Merging를 방지하면 조인 조건 Pushdown이 작동
  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');

실행계획