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
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