This discussion is archived
4 Replies Latest reply: May 27, 2009 4:27 AM by castorp RSS

Ansi JOIN not the same as "old style" join in MView?

castorp Explorer
Currently Being Moderated
Hello,

we are trying to create a fast refreshable mview but it seems that Oracle has a bug when it comes to determine the JOINs in a SELECT when using ansi joins.

Consider this
SQL> create table test1
  2  (
  3    id1 varchar(10) primary key,
  4    testcol1 varchar(10)
  5  );

Table created.

SQL>
SQL> create table test2
  2  (
  3    id2 varchar(10) primary key,
  4    id1 varchar(10) ,
  5    testcol2 varchar(10)
  6  );

Table created.

SQL>
SQL> alter table test2 add foreign key (id1) references test1(id1);

Table altered.

SQL>
SQL> insert into test1 values('1.1', 'Arthur');

1 row created.

SQL> insert into test2 values('2.1', '1.1', 'Zaphod');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create materialized view log on test1 with rowid including new values;

Materialized view log created.

SQL> create materialized view log on test2 with rowid including new values ;

Materialized view log created.

SQL>
SQL> CREATE MATERIALIZED VIEW testview
  2  REFRESH FAST ON COMMIT WITH ROWID
  3  as
  4  SELECT t1.id1 as t1_id1,
  5         t1.testcol1,
  6         t2.id2 as t2_id2,
  7         t1.rowid as t1_rowid,
  8         t2.rowid as t2_rowid
  9  FROM test1 t1 INNER JOIN test2 t2 ON t2.id1 = t1.id1
 10  ;
FROM test1 t1 INNER JOIN test2 t2 ON t2.id1 = t1.id1
     *
ERROR at line 9:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
According to the manual all restrictions for a fast refreshable MVIEW are met (rowids are in the log and in the select list, no sub-selects, no select list subquery) but still it fails.

When I leave out the ON COMMIT part, I get the error message: ORA-12015: cannot create a fast refresh materialized view from a complex query

When changing the ANSI JOIN into an old fashioned join it is working:
SQL> CREATE MATERIALIZED VIEW testview
  2  REFRESH FAST ON COMMIT WITH ROWID
  3  as
  4  SELECT t1.id1 as t1_id1,
  5         t1.testcol1,
  6         t2.id2 as t2_id2,
  7         t1.rowid as t1_rowid,
  8         t2.rowid as t2_rowid
  9  FROM test1 t1, test2 t2
 10  WHERE t2.id1 = t1.id1
 11  ;

Materialized view created.
Which does not make sense at all as both SELECT statements are functionally 100% identical

This smells like a bug to me...

I have tested this with 11.1.0.6.0 on Windows and 10.2.0.3.0 on Linux

Regards
Thomas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points