CREATE materialized VIEW emp_vw
(ename, dname, job, sal, comm, deptno)
AS SELECT IV.emp_name,
          IV.job,
          IV.salary,
          IV.commission,
          IV.department_no,
          IV.department_name
   FROM   (SELECT emp.ename  AS EMP_NAME,
                  emp.job    AS JOB,
                  emp.sal    AS SALARY,
                  emp.comm   AS COMMISSION,
                  emp.deptno AS DEPARTMENT_NO,
                  dept.dname AS DEPARTMENT_NAME
           FROM   scott.emp
                  inner join scott.dept
                          ON emp.deptno = dept.deptno
                             AND emp.deptno IN( 10, 20 )
           ORDER  BY emp.deptno) IV;

orclz> CREATE materialized VIEW emp_vw
  2  (ename, dname, job, sal, comm, deptno)
  3  AS SELECT IV.emp_name,
  4            IV.job,
  5            IV.salary,
  6            IV.commission,
  7            IV.department_no,
  8            IV.department_name
  9     FROM   (SELECT emp.ename  AS EMP_NAME,
 10                    emp.job    AS JOB,
 11                    emp.sal    AS SALARY,
 12                    emp.comm   AS COMMISSION,
 13                    emp.deptno AS DEPARTMENT_NO,
 14                    dept.dname AS DEPARTMENT_NAME
 15             FROM   scott.emp
 16                    inner join scott.dept
 17                            ON emp.deptno = dept.deptno
 18                               AND emp.deptno IN( 10, 20 )
 19             ORDER  BY emp.deptno) IV;

Materialized view created.

orclz> select * from emp_vw;

ENAME      DNAME            JOB        SAL       COMM DEPTNO
---------- --------- ---------- ---------- ---------- --------------
CLARK      MANAGER         2450                    10 ACCOUNTING
KING       PRESIDENT       5000                    10 ACCOUNTING
MILLER     CLERK           1300                    10 ACCOUNTING
JONES      MANAGER         2975                    20 RESEARCH
SCOTT      ANALYST         3000                    20 RESEARCH
ADAMS      CLERK           1100                    20 RESEARCH
FORD       ANALYST         3000                    20 RESEARCH

7 rows selected.

orclz>