无法创建具有联接和分组依据的实例化视图

Question

>我正在尝试创建一个具有左外部连接和分组依据的物化视图。

我收到此错误：

错误报告 - ORA-12015：无法创建具体化的快速刷新 从复杂查询查看 12015. 00000 - "无法从复杂查询创建快速刷新具体化视图">

原因：不支持 ROWID 和主键约束 复杂查询。

操作： 使用刷新强制或刷新完成重新发出命令 选项或创建简单的实例化视图。

代码：

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;

Answer 1

平行的东西表明这些是大桌子。 因此，我建议您使用并行使用"预构建表"实例化它们，然后切换到串行以进行快速刷新 - 因为在快速刷新期间的重型并行似乎违反直觉。

确保您的 mview 日志包含正确类型的信息 - 然后应该可以进行外部联接。

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>

您可以使用DBMS_MVIEW。EXPLAIN_MVIEW提前评估您的 mview 的各种功能。