4 Replies Latest reply on May 27, 2009 11:27 AM by castorp

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

    castorp
      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