2.31 GBEP (Group By Extension Pruning) 불필요한 Rollup 이나 CUBE를 삭제하라

• 오라클 Transformer는 Rollup이나 Cube등의 Group by Exension기능을 사용할 때 불필요한 Rollup이나 Cube를 제거한다.

============
Plan Table
============

Predicate Information (identified by operation id):

3 - access("E"."JOB_ID"='MK_REP')

* Plan상에서 Rollup이 사라졌다. Transformer가 SQL을 아래처럼 바꿈 |
| {code:SQL|title= SQL변환 |borderStyle=solid}
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 where e.job_id = 'MK_REP'
 group by e.department_id, e.job_id;

============
Plan Table
============
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|   1 |  HASH GROUP BY               |            |      1 |      1 |00:00:00.01 |       2 |   763K|   763K|  394K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX |      1 |      1 |00:00:00.01 |       1 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("E"."JOB_ID"='MK_REP')

• 이러한 현상은 Rollup이나 Cube 사용 시 Group by단위에 포함된 컬럼 중 가장 오른쪽 컬럼을 인라인뷰 외부에서 동등조건으로 사용하는 경우 발생한다.
• 가장오른쪽 컬럼이 아닌 다른 컬럼을 사용하게 되면 아래처럼 Rollup이 나타나게 된다

============
Plan Table
============

Predicate Information (identified by operation id):

4 - access("E"."DEPARTMENT_ID"=12000)

* 이러한 현상이 나타나는 이유 Rollup을 논리적으로 풀어볼 경우 확실하게 알 수 있다.
{code:SQL|title= Rollup 비교 |borderStyle=solid}
--변환전
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 group by rollup(e.department_id, e.job_id);
--변환후
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 group by e.department_id, e.job_id
union all
select e.department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
 group by e.department_id
union all
select NULL department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
 group by null 

-- 상수조건을 주면
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 where e.job_id = 'MK_REP'
 group by e.department_id, e.job_id
union all
select e.department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
 where NULL = 'MK_REP' -- Null 비교는 항상 False이므로 
 group by e.department_id
union all
select NULL department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e 
where NULL = 'MK_REP'  -- Null 비교는 항상 False이므로 
 group by null ;

|

Cube 가장 오른쪽의 컬럼이 인라인뷰 외부에서 동등조건으로 사용되었을 때 동일한 원리로 Cube삭제되고 레벨이 낮은 Rollup으로 변경되는 것을 살펴볼 수 있다.

select  e1.department_id, e1.job_id, e1.avg_sal
  from (select e.department_id, e.job_id, avg(e.salary) avg_sal
          from employees e
         group by cube(e.department_id, e.job_id)) e1
 where e1.job_id = 'MK_REP';

============
Plan Table
============
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |      2 |00:00:00.01 |       2 |       |       |          |
|   1 |  VIEW                         |            |      1 |      2 |00:00:00.01 |       2 |       |       |          |
|   2 |   SORT GROUP BY ROLLUP        |            |      1 |      2 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |      1 |      1 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | EMP_JOB_IX |      1 |      1 |00:00:00.01 |       1 |       |       |          |
------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("E"."JOB_ID"='MK_REP')

• Cube가 Rollup으로 변환하는 현상을 논리적으로 풀어볼어 비교해보자.

-- 변환전
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 group by cube(e.department_id, e.job_id);
-- 변환후 
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 group by e.department_id, e.job_id
union all
select NULL department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 group by e.job_id
union all
select e.department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
 group by e.department_id
union all
select NULL department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e;
 
-- 상수조건을 주면
select e.department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
 where e.job_id = 'MK_REP'  
 group by e.department_id, e.job_id
union all
select NULL department_id, e.job_id, avg(e.salary) avg_sal
  from employees e
where e.job_id = 'MK_REP'
 group by e.job_id
union all
select e.department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
where NULL = 'MK_REP'   -- Null 비교는 항상 False이므로
 group by e.department_id
union all
select NULL department_id, NULL job_id, avg(e.salary) avg_sal
  from employees e
where NULL = 'MK_REP'   -- Null 비교는 항상 False이므로
 group by null;