SCOTT @ orcl2 > create materialized view deptno_emp_man
2 enable query rewrite
3 as
4 SELECT e.ename AS employee_name,
5 dc1.dept_count AS emp_dept_count,
6 m.ename AS manager_name,
7 dc2.dept_count AS mgr_dept_count
8 FROM emp e,
9 (SELECT deptno, COUNT(*) AS dept_count
10 FROM emp GROUP BY deptno) dc1,
11 emp m,
12 (SELECT deptno, COUNT(*) AS dept_count
13 FROM emp GROUP BY deptno) dc2
14 WHERE e.deptno = dc1.deptno
15 AND e.mgr = m.empno
16 AND m.deptno = dc2.deptno;
SCOTT @ orcl2 > explain plan for
2 select *
3 from deptno_emp_man;
SCOTT @ orcl2 > select *
2 from table(dbms_xplan.display);
SCOTT @ orcl2 > select *
2 from deptno_emp_man;
SCOTT @ orcl2 > SELECT e.ename AS employee_name,
2 dc1.dept_count AS emp_dept_count,
3 m.ename AS manager_name,
4 dc2.dept_count AS mgr_dept_count
5 FROM emp e,
6 (SELECT deptno, COUNT(*) AS dept_count
7 FROM emp GROUP BY deptno) dc1,
8 emp m,
9 (SELECT deptno, COUNT(*) AS dept_count
10 FROM emp GROUP BY deptno) dc2
11 WHERE e.deptno = dc1.deptno
12 AND e.mgr = m.empno
13 AND m.deptno = dc2.deptno;