Skip to Main Content
  • Questions
  • Can't create materialized view using left join and group by

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Kessy.

Asked: March 01, 2018 - 7:55 pm UTC

Last updated: March 02, 2018 - 10:57 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I'm trying to create a materialized view that joins two tables then groups by various ids but I keep running into this error:

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


I tried this:


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;


I get an error in the ide saying I need to add the rowid's to the group by but that doesn't fix it

and Chris said...

There are numerous restrictions regarding the type of query possible in a fast refresh MV. These vary by version. MOS note 179466.1 has a detailed explanation for releases up to 11.2.

You can find out precisely why your MV is failing using dbms_mview. Pass this your create materialized view statement, and it'll tell you the problems:

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          <null>         <null>                                                                               
REFRESH_COMPLETE                Y          <null>         <null>                                                                               
REFRESH_FAST                    N          <null>         <null>                                                                               
REWRITE                         N          <null>         <null>                                                                               
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          <null>         see the reason why REFRESH_FAST_AFTER_INSERT is disabled                             
REFRESH_FAST_AFTER_ONETAB_DML   N          <null>         mv uses the MIN or MAX aggregate functions                                           
REFRESH_FAST_AFTER_ONETAB_DML   N          <null>         COUNT(*) is not present in the select list                                           
REFRESH_FAST_AFTER_ANY_DML      N          <null>         see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled                         
REFRESH_FAST_PCT                N          <null>         PCT is not possible on any of the detail tables in the materialized view             
REWRITE_FULL_TEXT_MATCH         N          <null>         query rewrite is disabled on the materialized view                                   
REWRITE_PARTIAL_TEXT_MATCH      N          <null>         query rewrite is disabled on the materialized view                                   
REWRITE_GENERAL                 N          <null>         query rewrite is disabled on the materialized view                                   
REWRITE_PCT                     N          <null>         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 


To create the MV_CAPABILITIES_TABLE, run the following script:

@$ORACLE_HOME/rdbms/admin/utlxmv.sql

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.