Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORACLE : Materialized view not working when Using LEFT JOIN

I want to create a MATERIALIZED VIEW from a LEFT JOIN of 2 tables. However the following gives me an error:

    SELECT field1 
     FROM table_1 a 
     LEFT JOIN table_2 b 
     ON a.field1=b.field2

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

However the following works:

SELECT field1 
 FROM table_1 a, table_2 b 
 WHERE a.field1=b.field2

Does anyone have any ideas why this is happening.

Thx for the help

like image 269
ddallala Avatar asked Aug 21 '09 16:08

ddallala


People also ask

Can we use join in materialized view?

One of the most common follow-on questions we receive is whether materialized views can support joins. The answer is emphatically yes.

What causes a materialized view to become invalid?

When an MV is created, the materialized view depends on the master tables referenced in its definition. Any DML operation, such as an INSERT, or DELETE, UPDATE, or DDL operation on any dependency in the materialized view will cause it to become invalid.

How do you trigger a materialized view?

create table t ( c1 int primary key ); create materialized view log on t with rowid including new values; create materialized view mv refresh fast on demand as select count(*) from t; create or replace trigger t_aiuder after insert or update or delete on t declare pragma autonomous_transaction; begin if inserting then ...

What privilege is required for materialized view?

To create a materialized view in another user's schema: You must have the CREATE ANY MATERIALIZED VIEW system privilege. The owner of the materialized view must have the CREATE TABLE system privilege.


2 Answers

There are two conditions that are not satisfied to make that materialized view refresh fast. First one is that you did not specify the rowid columns of every table involved. And the second one is an undocumented restriction: ANSI-joins are not supported.

Here is an example with DEPT being table_1, alias a and EMP being table_2, alias b:

SQL> create materialized view log on emp with rowid
  2  /

Materialized view log created.

SQL> create materialized view log on dept with rowid
  2  /

Materialized view log created.

SQL> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select a.deptno
  5    from dept a
  6         left join emp b on (a.deptno = b.deptno)
  7  /
  from dept a
       *
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

That mimics your situation. First add the rowid's:

SQL> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select a.rowid dept_rowid
  5       , b.rowid emp_rowid
  6       , a.deptno
  7    from dept a
  8         left join emp b on (a.deptno = b.deptno)
  9  /
  from dept a
       *
ERROR at line 7:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Still it cannot fast refresh, because of the ANSI joins. Converting to old-style outer join syntax:

SQL> create materialized view empdept_mv
  2    refresh fast on commit
  3  as
  4  select a.rowid dept_rowid
  5       , b.rowid emp_rowid
  6       , a.deptno
  7    from dept a
  8       , emp b
  9   where a.deptno = b.deptno (+)
 10  /

Materialized view created.

And to prove that it works:

SQL> select * from empdept_mv
  2  /

DEPT_ROWID         EMP_ROWID              DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB         30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE         30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF         30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG         10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH         20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI         10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM         20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN         10
AAARhmAAEAAAAI/AAD                            40

15 rows selected.

SQL> insert into dept values (50,'IT','UTRECHT')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from empdept_mv
  2  /

DEPT_ROWID         EMP_ROWID              DEPTNO
------------------ ------------------ ----------
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAA         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAB         30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAC         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAD         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAE         30
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAF         30
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAG         10
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAH         20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAI         10
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAJ         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAK         20
AAARhmAAEAAAAI/AAC AAARhlAAEAAAAI3AAL         30
AAARhmAAEAAAAI/AAB AAARhlAAEAAAAI3AAM         20
AAARhmAAEAAAAI/AAA AAARhlAAEAAAAI3AAN         10
AAARhmAAEAAAAI/AAD                            40
AAARhmAAEAAAAI7AAA                            50

16 rows selected.

The ANSI-join syntax restriction is mentioned in point 6 in this blogpost.

Regards, Rob.

like image 124
Rob van Wijk Avatar answered Sep 24 '22 23:09

Rob van Wijk


Since this is an old post; no has mentioned complete solution.

  1. The table that is outer joined should have a primary key as mentioned in Oracle doc.
  2. The query should not have any other constraints i.e should not have any filter criteria in WHERE clause, just the joins; nor can have CASE/DECODE statements in SELECT clause; GROUP BY, SUM(), COUNT() and such are allowed, though.

In above sample example the query will work if a primary key is created on department table on dept id column.

like image 41
ron Avatar answered Sep 24 '22 23:09

ron