Join Predicate Push Down (JPPD) 는 Group By 가 있는 View 도 침투 가능 (1)

JPPD 는 GROUP BY 가 있는 뷰에도 침투 가능합니다. LATERAL 재작성으로 옵티마이저의 변환 결과를 명시적으로 표현할 수 있습니다.

핵심 정리

1. GROUP BY 가 있는 view 에도 view 바깥의 조인 조건을 view 내부로 침투시킬 수 있습니다.
2. 아래 예시를 보면 기존 SELECT 절과 GROUP BY 에 있던 e.department_id 가 LATERAL 재작성 시 생략됩니다: 1) LATERAL 서브쿼리는 department d 의 각 d.department_id 에 대해 개별적으로 실행됩니다. 2) WHERE e.department_id = d.department_id 조건으로 서브쿼리 내에서 e.department_id 는 항상 d.department_id 와 동일한 값을 가집니다. 3) 따라서 e.department_id 를 SELECT 절에 포함시키거나 GROUP BY 에 명시할 필요가 없습니다. 4) 왜냐하면 e.department_id 는 서브쿼리 실행 시 이미 고정된 값으로 작동하기 때문입니다.

결과적으로 LATERAL 때문에 서브쿼리가 d.department_id 단위로 실행되므로, e.department_id 를 그룹핑 기준으로 포함할 필요가 없습니다.

---

원본 쿼리

SELECT /*+ LEADING(d) USE_NL(e) */
       d.department_id, d.department_name, e.job_title, e.sum_sal, max_sal
  FROM department d,
       (SELECT e.department_id, e.job_id, MIN(j.job_title) job_title,
               SUM(e.salary) sum_sal, MAX(e.salary) max_sal
          FROM employee e, job j
         WHERE e.job_id = j.job_id
         GROUP BY e.department_id, e.job_id) e
 WHERE d.department_id = e.department_id(+);

실행 계획

-------------------------------------------------------------------------------------
| Id | Operation                          | Name              | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |                   |      |       |  138 |
|  1 |  NESTED LOOPS OUTER                |                   |   97 |  6693 |  138 |
|  2 |   TABLE ACCESS FULL                | DEPARTMENT        |   27 |   540 |    3 |
|  3 |   VIEW PUSHED PREDICATE            |                   |    1 |    49 |    5 |
|  4 |    SORT GROUP BY                   |                   |    6 |   312 |    5 |
|  5 |     MERGE JOIN                     |                   |    9 |   468 |    5 |
|  6 |      TABLE ACCESS BY INDEX ROWID   | JOB               |   19 |   513 |    2 |
|  7 |       INDEX FULL SCAN              | JOB_ID_PK         |   19 |       |    1 |
|  8 |      SORT JOIN                     |                   |   10 |   250 |    3 |
|  9 |       TABLE ACCESS BY INDEX ROWID  | EMPLOYEE          |   10 |   250 |    2 |
| 10 |        INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |   10 |       |    1 |
-------------------------------------------------------------------------------------

Predicate Information:
8  - access("E"."JOB_ID"="J"."JOB_ID")
8  - filter("E"."JOB_ID"="J"."JOB_ID")
10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

Id 3 의 VIEW PUSHED PREDICATE 가 핵심 — 옵티마이저가 외부의 d.department_id = e.department_id 술어를 뷰 안의 GROUP BY 위로 밀어 넣었습니다. 그 결과 Id 10 의 EMP_DEPARTMENT_IX RANGE SCAN 으로 부서당 필요한 사원만 추려져 NL OUTER 의 안쪽 가지가 인덱스 액세스로 풀렸습니다.

---

수정 (재작성) 쿼리

LATERAL 서브쿼리로 옵티마이저의 JPPD 변환을 사람이 직접 표현한 형태입니다. e.department_id 가 SELECT 절과 GROUP BY 에서 모두 빠진 점을 주목해야 합니다.

SELECT /*+ LEADING(D) USE_NL(E) */
       d.department_id, d.department_name, e.job_title, e.sum_sal, max_sal
  FROM department d,
       LATERAL (SELECT e.job_id, MIN(j.job_title) job_title,
                       SUM(e.salary) sum_sal, MAX(e.salary) max_sal
                  FROM employee e, job j
                 WHERE e.job_id = j.job_id
                   AND e.department_id = d.department_id
                 GROUP BY e.job_id) (+) e;

---

두 쿼리의 차이

1. 첫 번째 쿼리 는 “모든 부서와 직원의 데이터를 한꺼번에 모아서 부서별 (department_id) + 직무별 (job_id) 로 정리한 뒤, 부서 테이블과 매칭” 하는 방식입니다.
2. 두 번째 쿼리 는 “각 부서 (d.department_id) 를 하나씩 살펴보면서, 그 부서에 속한 직원들만 따로 모아 직무별로 정리” 하는 방식입니다.

LATERAL 서브쿼리가 d.department_id 를 상수처럼 취급한다는 점을 기억해야 합니다. 그래서 GROUP BY 키에서 department_id 가 빠지더라도 결과는 동등합니다.