Oracle 无法使用左联接和group by创建实体化视图

我正在尝试创建一个实体化视图，该视图将两个表连接起来，然后按各种id进行分组，但我一直遇到此错误:

ORA-12015: Neither ROWIDs and nor primary key constraints are supported for queries.
复制

我试过这个:

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,
            DT.ROWID DT_ROWID,
            AD.ROWID AD_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;
复制

我在ide中出现错误，说我需要将rowid添加到组中，但这并不能解决

关于快速刷新MV中可能的查询类型有许多限制。这些因版本而异。MOS note 179466.1对高达11.2的版本有详细的解释。

您可以准确地找出为什么您的MV使用dbms_mview失败。通过这个你的创建实体化视图语句，它会告诉你的问题:

create table t (
  x int primary key, y int
);

truncate table MV_CAPABILITIES_TABLE;
begin 
  dbms_mview.explain_mview (
    'create materialized view mv refresh fast on demand as
select y, max(x) from t
group  by y'
  );
end;
/
select capability_name, possible, related_text, msgtxt 
from   MV_CAPABILITIES_TABLE;

CAPABILITY_NAME                 POSSIBLE   RELATED_TEXT   MSGTXT                                                                               
PCT                             N                                                                                                  
REFRESH_COMPLETE                Y                                                                                                  
REFRESH_FAST                    N                                                                                                  
REWRITE                         N                                                                                                  
PCT_TABLE                       N          T              relation is not a partitioned table                                                  
REFRESH_FAST_AFTER_INSERT       N          CHRIS.T        the detail table does not have a materialized view log                               
REFRESH_FAST_AFTER_ONETAB_DML   N                   see the reason why REFRESH_FAST_AFTER_INSERT is disabled                             
REFRESH_FAST_AFTER_ONETAB_DML   N                   mv uses the MIN or MAX aggregate functions                                           
REFRESH_FAST_AFTER_ONETAB_DML   N                   COUNT(*) is not present in the select list                                           
REFRESH_FAST_AFTER_ANY_DML      N                   see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                         
REFRESH_FAST_PCT                N                   PCT is not possible on any of the detail tables in the materialized view             
REWRITE_FULL_TEXT_MATCH         N                   query rewrite is disabled on the materialized view                                   
REWRITE_PARTIAL_TEXT_MATCH      N                   query rewrite is disabled on the materialized view                                   
REWRITE_GENERAL                 N                   query rewrite is disabled on the materialized view                                   
REWRITE_PCT                     N                   general rewrite is not possible or PCT is not possible on any of the detail tables   
PCT_TABLE_REWRITE               N          T              relation is not a partitioned table 
复制

要创建MV_CAPABILITIES_TABLE，请运行以下脚本:

@ $ ORACLE_HOME/rdbms/admin/utlxmv.sql