SQL> SQL> /* DOC> DOC>When are filter columns needed? DOC>In a single table aggreagte MV, all columns in the select list must be included in the MV LOG. DOC> DOC>*/ SQL> select ename,sal,deptno from emp_a; ENAME SAL DEPTNO ---------- ---------- ---------- ALLEN 1600 30 WARD 1250 30 MARTIN 1250 30 SCOTT 3000 20 KING 5000 10 TURNER 1500 30 FORD 3000 20 DAVID 6000 10 jusung 9999.99 10 JOHN 10000 10 10 rows selected. SQL> alter table emp_a add constraint emp_a_pk primary key (empno); Table altered. SQL> create materialized view log on emp_a; Materialized view log created. SQL> SQL> -- ORA-12032: cannot use rowid column from materialized view log on .. SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; from emp_a group by deptno * ERROR at line 4: ORA-12032: cannot use rowid column from materialized view log on "JYANG"."EMP_A" SQL> SQL> alter materialized view log on emp_a add rowid; Materialized view log altered. SQL> SQL> -- ORA-32401: materialized view log on "JYANG"."EMP_A" does not have new values SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; from emp_a group by deptno * ERROR at line 4: ORA-32401: materialized view log on "JYANG"."EMP_A" does not have new values SQL> SQL> -- Can not specify "new values" alone... SQL> alter materialized view log on emp_a including new values; alter materialized view log on emp_a including new values * ERROR at line 1: ORA-12045: invalid ALTER MATERIALIZED VIEW LOG option SQL> SQL> drop materialized view log on emp_a; Materialized view log dropped. SQL> create materialized view log on emp_a 2 with primary key, rowid 3 including new values; Materialized view log created. SQL> SQL> -- ORA-12033: cannot use filter columns from materialized view log on.. SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; from emp_a group by deptno * ERROR at line 4: ORA-12033: cannot use filter columns from materialized view log on "JYANG"."EMP_A" SQL> SQL> -- Add filter columns SQL> alter materialized view log on emp_a add(deptno); Materialized view log altered. SQL> -- ORA-12033: cannot use filter columns from materialized view log on.. SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; from emp_a group by deptno * ERROR at line 4: ORA-12033: cannot use filter columns from materialized view log on "JYANG"."EMP_A" SQL> SQL> drop materialized view log on emp_a; Materialized view log dropped. SQL> -- Incude 1 filter column in the log... SQL> create materialized view log on emp_a 2 with primary key, rowid(sal) 3 including new values; Materialized view log created. SQL> SQL> -- ORA-12033: cannot use filter columns from materialized view log on.. SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; from emp_a group by deptno * ERROR at line 4: ORA-12033: cannot use filter columns from materialized view log on "JYANG"."EMP_A" SQL> SQL> -- Add another filter column to the LOG SQL> alter materialized view log on emp_a add(deptno); Materialized view log altered. SQL> -- Now OK SQL> create materialized view emp_a_mv 2 refresh fast on commit as 3 select count(*), sum(sal), deptno, count(sal) 4 from emp_a group by deptno; Materialized view created. SQL> SQL> select * from emp_a_mv; COUNT(*) SUM(SAL) DEPTNO COUNT(SAL) ---------- ---------- ---------- ---------- 4 30999.99 10 4 2 6000 20 2 4 5600 30 4 SQL> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews 2 where mview_name='EMP_A_MV'; LAST_REF LAST_REFRESH_DA -------- --------------- COMPLETE 270703 10:33:16 SQL> update emp_a set sal=100; 10 rows updated. SQL> select * from mlog$_emp_a; EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 7499 1600 AAANz6AAJAAAATsAAA 010100 00:00:00 U O 4000 30 7499 100 AAANz6AAJAAAATsAAA 010100 00:00:00 U N 4000 30 7521 1250 AAANz6AAJAAAATsAAB 010100 00:00:00 U O 4000 30 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 7521 100 AAANz6AAJAAAATsAAB 010100 00:00:00 U N 4000 30 7654 1250 AAANz6AAJAAAATsAAC 010100 00:00:00 U O 4000 30 7654 100 AAANz6AAJAAAATsAAC 010100 00:00:00 U N 4000 30 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 7788 3000 AAANz6AAJAAAATsAAD 010100 00:00:00 U O 4000 20 7788 100 AAANz6AAJAAAATsAAD 010100 00:00:00 U N 4000 20 7839 5000 AAANz6AAJAAAATsAAE 010100 00:00:00 U O 4000 10 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 7839 100 AAANz6AAJAAAATsAAE 010100 00:00:00 U N 4000 10 7844 1500 AAANz6AAJAAAATsAAF 010100 00:00:00 U O 4000 30 7844 100 AAANz6AAJAAAATsAAF 010100 00:00:00 U N 4000 30 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 7902 3000 AAANz6AAJAAAATsAAG 010100 00:00:00 U O 4000 20 7902 100 AAANz6AAJAAAATsAAG 010100 00:00:00 U N 4000 20 1002 6000 AAANz6AAJAAAATsAAH 010100 00:00:00 U O 4000 10 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 1002 100 AAANz6AAJAAAATsAAH 010100 00:00:00 U N 4000 10 777 9999.99 AAANz6AAJAAAATsAAI 010100 00:00:00 U O 4000 10 777 100 AAANz6AAJAAAATsAAI 010100 00:00:00 U N 4000 10 EMPNO SAL M_ROW$$ SNAPTIME$$ ---------- ---------- ------------------------------ -------------------- DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ DEPTNO ---------- ---------- ---------------- ---------- 1001 10000 AAANz6AAJAAAATsAAJ 010100 00:00:00 U O 4000 10 1001 100 AAANz6AAJAAAATsAAJ 010100 00:00:00 U N 4000 10 20 rows selected. SQL> commit; Commit complete. SQL> select * from emp_a_mv; COUNT(*) SUM(SAL) DEPTNO COUNT(SAL) ---------- ---------- ---------- ---------- 4 400 10 4 2 200 20 2 4 400 30 4 SQL> select LAST_REFRESH_TYPE, LAST_REFRESH_DATE from user_mviews 2 where mview_name='EMP_A_MV'; LAST_REF LAST_REFRESH_DA -------- --------------- FAST 270703 10:33:16 SQL> SQL> drop table emp_a; Table dropped. SQL> drop materialized view emp_a_mv; Materialized view dropped. SQL> SQL> spool off