Materialized view on super aggregates

Very recently working in a project, observed that a client using an excellent technique using materialized views and super aggregate functions.

Basically they resolved all entries from fact tables into fine granules of dimensions along with all combinations and aggregated the results into materialized views. It looks like this.

rajesh@ORA10G> select deptno,job,sum(sal),

  2     count(sal), count(*), max(sal),

  3     min(sal), avg(sal) , grouping_id(deptno,job) grp

  4  from emp

  5  group by cube(deptno,job)

  6  order by grp ;

    DEPTNO JOB         SUM(SAL) COUNT(SAL)   COUNT(*)   MAX(SAL)   MIN(SAL)   AVG(SAL)        GRP

---------- --------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

        10 MANAGER         2450          1          1       2450       2450       2450          0

        30 MANAGER         2850          1          1       2850       2850       2850          0

        30 CLERK            950          1          1        950        950        950          0

        20 MANAGER         2975          1          1       2975       2975       2975          0

        20 ANALYST         6000          2          2       3000       3000       3000          0

        20 CLERK           1900          2          2       1100        800        950          0

        10 PRESIDENT       5000          1          1       5000       5000       5000          0

        30 SALESMAN        5600          4          4       1600       1250       1400          0

        10 CLERK           1300          1          1       1300       1300       1300          0

        20                10875          5          5       3000        800       2175          1

        30                 9400          6          6       2850        950 1566.66667          1

        10                 8750          3          3       5000       1300 2916.66667          1

           PRESIDENT       5000          1          1       5000       5000       5000          2

           SALESMAN        5600          4          4       1600       1250       1400          2

           MANAGER         8275          3          3       2975       2450 2758.33333          2

           ANALYST         6000          2          2       3000       3000       3000          2

           CLERK           4150          4          4       1300        800     1037.5          2

                          29025         14         14       5000        800 2073.21429          3

18 rows selected.

rajesh@ORA10G>

When the end users perform queries for any aggregation over any combination of dimensions, optimizer rewrites them using appropriate filter against materialized views. So it appears like this.

rajesh@ORA10G> create materialized view emp_mv

  2  build immediate

  3  refresh on demand

  4  enable query rewrite

  5  as

  6  select deptno,job,sum(sal),

  7     count(sal), count(*), max(sal),

  8     min(sal), avg(sal) , grouping_id(deptno,job) grp

  9  from emp

 10  group by cube(deptno,job);

Materialized view created.

rajesh@ORA10G> exec dbms_stats.gather_table_stats(user,'emp_mv');

PL/SQL procedure successfully completed.

rajesh@ORA10G>

Now, if we perform any aggregation over the dimension of deptno or job on "Emp" table, they will be rewritten using materialized view.

rajesh@ORA10G> set autotrace on explain

rajesh@ORA10G>

rajesh@ORA10G> select deptno, count(*)

  2  from emp

  3  group by rollup(deptno);

    DEPTNO   COUNT(*)

---------- ----------

                   14

        10          3

        20          5

        30          6

4 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1199708657

---------------------------------------------------------------------------------------

| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |        |     9 |    81 |     3   (0)| 00:00:01 |

|*  1 |  MAT_VIEW REWRITE ACCESS FULL| EMP_MV |     9 |    81 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("EMP_MV"."GRP"=1 OR "EMP_MV"."GRP"=3)

rajesh@ORA10G>