EddieAwad.com

Think Twice When Creating a Materialized View with the ANSI Join Syntax

When we attempt to create a materialized view with the ANSI join syntax we are surprisingly rewarded with an ORA error.

create materialized view mv2
  refresh fast
  as
    select
      t.key    t_key     ,
      t.val    t_val     ,
      t2.key   t2_key    ,
      t2.amt   t2_amt    ,
      t.rowid  t_row_id  ,
      t2.rowid t2_row_id
    from
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
;
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
      *
ERROR at line 12:
ORA-12015: cannot create a fast refresh materialized view from a complex query

While this behaviour appears to be a bug at first glance, Metalink note 420856.1 explains that it is really an undocumented limitation of fast refresh materialized views.

An examination of the EXPLAIN_MVIEW results for this case points to some behind-the-scenes transformations with ANSI syntax which may be causing the limitation.

select
  my_mv_capabilities
  ( 'create materialized view mv2
    refresh fast
    as
      select
        t.key    t_key     ,
        t.val    t_val     ,
        t2.key   t2_key    ,
        t2.amt   t2_amt    ,
        t.rowid  t_row_id  ,
        t2.rowid t2_row_id
      from
        T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )'
  , 'REFRESH_FAST_AFTER_INSERT'
  ) as mv_report
from dual ;