When executing the following code on the SCOTT schema I receive a compilation_error. What am I doing wrong here? I doesn't even tell me what the error is.

SQL> create materialized view dept_emp_mv as (  
  2      select dname, job, hiredate, count(*) nbr  
  3                  from emp e  
  4                  join dept d on e.deptno = d.deptno  
  5                  group by dname,hiredate,job);  
Materialized view created.  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  
SQL> insert into emp values (8003, 'TEST', 'TEST', 7902, to_date('20131127','YYYYMMDD'), 2500, null, 20);  
1 row created.  
SQL> commit;  
Commit complete.  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  
SQL> execute DBMS_SNAPSHOT.REFRESH('DEPT_EMP_MV','C', parallelism => 1);  
PL/SQL procedure successfully completed.  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  

Any help is appreciated.


With Oracle 12c this issue seems to be resolved.

  • Explain plan the query in the materialised view to make sure it's parsing correctly. Have you any triggers on the table that could be causing dependant object invalidation? – Philᵀᴹ Nov 28 '13 at 15:29
  • Actually, try refreshing it again. I bet it changes back and is ok. – Philᵀᴹ Nov 28 '13 at 15:30
  • Refreshing it again still causes the compilation error. There are no triggers on the table, this table comes from the demo schema 'SCOTT' – Pmarcoen Nov 28 '13 at 15:33
  • Executing the query outside of the materialized view works perfectly and displays a correct result. The explain plan contains a hash, merge join and sort operation with a total cost of 7. This is a pretty basic query in my opinion. – Pmarcoen Nov 28 '13 at 15:36
  • After refreshing it again is the data actually correct? I remember seeing that status in the past, and in one situation it was ignorable – Philᵀᴹ Nov 28 '13 at 15:45

It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing...

Changing the definition to

create materialized view dept_emp_mv as  
   select dname, job, hiredate, count(*) as nbr  
               from emp e, dept d
               where e.deptno = d.deptno
               group by dname,hiredate,job;

makes it work for me.

See http://sqlfiddle.com/#!4/f706b/1

Changing the definition in the SQL Fiddle back to the one included in the question will cause the staleness to be COMPILATION_ERROR.

Personally, I would classify this as a work-around rather than an answer.

  • That is indeed a good work-around. I find it very strange though that Oracle cannot handle a JOIN, since it is perfectly correct syntax. – Pmarcoen Dec 2 '13 at 12:01
  • My sentiments exactly and would like to know why the ANSI-join (at least in this case) results in a "compilation error". – Colin 't Hart Dec 2 '13 at 12:34
  • Good spot! Definitely a bug Oracle need to fix. – Philᵀᴹ Dec 4 '13 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.