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>
