Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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>  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  
STALENESS  
-------------------  
FRESH  
SQL>  
SQL> insert into emp values (8003, 'TEST', 'TEST', 7902, to_date('20131127','YYYYMMDD'), 2500, null, 20);  
1 row created.  
SQL>  
SQL> commit;  
Commit complete.  
SQL>  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  
STALENESS  
-------------------  
NEEDS_COMPILE  
SQL>  
SQL> execute DBMS_SNAPSHOT.REFRESH('DEPT_EMP_MV','C', parallelism => 1);  
PL/SQL procedure successfully completed.  
SQL>  
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';  
STALENESS  
-------------------  
COMPILATION_ERROR  

Any help is appreciated.

Update

With Oracle 12c this issue seems to be resolved.

share|improve this question
    
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

1 Answer 1

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.

share|improve this 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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