Can't create materialized view with a join and group-by

Question

I'm trying to create a materialized view with a left outer join and a group by.

I'm getting this error:

Error report - ORA-12015: cannot create a fast refresh materialized view from a complex query 12015. 00000 - "cannot create a fast refresh materialized view from a complex query"

Cause: Neither ROWIDs and nor primary key constraints are supported for complex queries.

Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.

The code:

CREATE MATERIALIZED VIEW CSPRD.MV_LLATTRDATA_MAX_VERSIONS
    PARALLEL 16
    USING INDEX 
    REFRESH 
    NEXT trunc(SYSDATE, 'hh') + 1/24      
    FAST 
    WITH ROWID 
    USING DEFAULT LOCAL ROLLBACK SEGMENT 
    ENABLE QUERY REWRITE 
    AS 
        SELECT /*+ PARALLEL(16) */ 
            AD.ID, 
            AD.DEFID, 
            AD.ATTRID,
            MAX(AD.VERNUM) MAX_VERNUM, 
            MAX(AD.DEFVERN) MAX_DEFVERN, 
            AD.ROWID, 
            DT.ROWID
        FROM  csprd.mv_llattrdata_shrunk_v1  AD, MV_DTREECORE_SHRUNK_V2 DT
        WHERE AD.ID = DT.DATAID(+)
        GROUP BY AD.ID, AD.DEFID, AD.ATTRID;

Connor McDonald Connor McDonald · Accepted Answer · 2018-03-02T01:21:26

The parallel stuff suggests these are big tables. So I'd suggest you instantiate them with "prebuilt table" using parallel, and then switch to serial for the fast refresh - because heavy duty parallel during a fast refresh would seem counter intuitive.

Make sure your mview logs contain the right kind of information - and then an outer join should be possible.

SQL> create table t as select * from dba_objects where object_id is not null;
SQL> alter table t add constraint t_pk primary key ( object_id ) ;
SQL> create table t1 as select * from dba_objects where object_id is not null;
SQL> alter table t1 add constraint t1_pk primary key ( object_id ) ;
SQL> create materialized view log on t with (object_type), sequence, rowid including new values;
SQL> create materialized view log on t1 with (object_type), sequence, rowid including new values;
SQL>
SQL> --
SQL> -- single table
SQL> --
SQL> create materialized view mv
  2  REFRESH   FAST
  3  as
  4  SELECT
  5      object_type,
  6      max(object_id) oid
  7  FROM  t
  8  GROUP BY object_type;

Materialized view created.

SQL>
SQL> --
SQL> -- simple join
SQL> --
SQL> drop materialized view mv;

Materialized view dropped.

SQL> create materialized view mv
  2  REFRESH   FAST
  3  as
  4  SELECT
  5      t.object_type,
  6      max(t.object_id) oid
  7  FROM  t, t1
  8  where t1.object_id = t.object_id
  9  GROUP BY t.object_type;

Materialized view created.

SQL>
SQL> --
SQL> -- outer join
SQL> --
SQL> drop materialized view mv;

Materialized view dropped.

SQL> create materialized view mv
  2  REFRESH   FAST
  3  as
  4  SELECT
  5      t.object_type,
  6      t1.object_type o2type,
  7      max(t.object_id) oid
  8  FROM  t, t1
  9  where t.object_id = t1.object_id(+)
 10  GROUP BY t.object_type, t1.object_type;

Materialized view created.

SQL>

You can use DBMS_MVIEW.EXPLAIN_MVIEW to assess what the various capabilities of your mview will be in advance.

Can't create materialized view with a join and group-by

1 Answers