Hello,
For performance needs i want to create a materialized view on commit refresh option using the following script:
CREATE TABLE DEVDV
(DEVDV_ID INTEGER PRIMARY kEY,
DEVDV_SRC_DVISE_ID INTEGER,
DEVDV_CIB_DVISE_ID INTEGER);
CREATE TABLE CONDV
(CONDV_ID INtEgEr PRiMARY KEY,
CONDV_DEVDV_iD INTEGER,
CONDV_TX NUMbeR,
CONDV_DATE_DEB datE,
CONDV_DATE_FIN DATE);
CREATE MATERIALIZED VIEW LOG ON DEVDV WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON CONDV WITH ROWID;
CREATE MATERIALIZED VIEW MV_DEVDV_TYP_2
REFRESH FAST
ON COMMIT
AS
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROWID,
DEVDV_ID,
1 AS MARKER,
DEVDV_SRC_DVISE_ID,
DEVDV_CIB_DVISE_ID,
CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV INNER JOIN CONDV ON DEVDV_ID = CONDV_DEVDV_ID
UNION ALL
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROW_ID,
DEVDV_ID,
2 AS MARKER,
DEVDV_CIB_DVISE_ID,
DEVDV_SRC_DVISE_ID,
1/CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV INNER JOIN CONDV ON DEVDV_ID = CONDV_DEVDV_ID;
Oracle says that it's a complex query and it doesn't meet the fast refresh requirements,
Could you please tell me wich rule i've broken?
Thank you
If you're struggling with MV refreshes, the best thing to do is use the DBMS_MVIEW.EXPLAIN_MVIEW procedure.
This tells you why various refreshes aren't possible:
CREATE TABLE DEVDV
(DEVDV_ID INTEGER PRIMARY kEY,
DEVDV_SRC_DVISE_ID INTEGER,
DEVDV_CIB_DVISE_ID INTEGER);
CREATE TABLE CONDV
(CONDV_ID INtEgEr PRiMARY KEY,
CONDV_DEVDV_iD INTEGER,
CONDV_TX NUMbeR,
CONDV_DATE_DEB datE,
CONDV_DATE_FIN DATE);
CREATE MATERIALIZED VIEW LOG ON DEVDV WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON CONDV WITH ROWID;
CREATE MATERIALIZED VIEW MV_DEVDV_TYP_2
AS
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROWID,
DEVDV_ID,
1 AS MARKER,
DEVDV_SRC_DVISE_ID,
DEVDV_CIB_DVISE_ID,
CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV INNER JOIN CONDV ON DEVDV_ID = CONDV_DEVDV_ID
UNION ALL
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROW_ID,
DEVDV_ID,
2 AS MARKER,
DEVDV_CIB_DVISE_ID,
DEVDV_SRC_DVISE_ID,
1/CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV INNER JOIN CONDV ON DEVDV_ID = CONDV_DEVDV_ID;
create table mv_capabilities_table (
statement_id varchar(30) ,
mvowner varchar(30) ,
mvname varchar(30) ,
capability_name varchar(30) ,
possible character(1) ,
related_text varchar(2000) ,
related_num number ,
msgno integer ,
msgtxt varchar(2000) ,
seq number
) ;
exec dbms_mview.explain_mview('MV_DEVDV_TYP_2');
select capability_name, possible, msgtxt from MV_CAPABILITIES_TABLE;
CAPABILITY_NAME POSSIBLE MSGTXT
PCT N <null>
REFRESH_COMPLETE Y <null>
REFRESH_FAST N <null>
REWRITE N <null>
REFRESH_FAST_AFTER_INSERT N inline view or subquery in FROM list not supported for this type MV
REFRESH_FAST_AFTER_INSERT N some query block in UNION ALL MV is not fast refreshable
REFRESH_FAST_AFTER_INSERT N set operator in a context not supported for fast refresh
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N PCT FAST REFRESH is not possible if query has set operand query blocks
REWRITE_FULL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH N set operator encountered in mv
REWRITE_PARTIAL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_GENERAL N set operator encountered in mv
REWRITE_GENERAL N the reason why the capability is disabled has escaped analysis
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 And review the refresh restrtions in the docs
https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-505C24CF-5D56-4820-88AA-2221410950E7 It's not particularly obvious from this. But there are still some issues with ANSI joins and materialized views.
Convert to Oracle style and all should be good:
DROP MATERIALIZED VIEW MV_DEVDV_TYP_2;
CREATE MATERIALIZED VIEW MV_DEVDV_TYP_2
AS
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROWID,
DEVDV_ID,
1 AS MARKER,
DEVDV_SRC_DVISE_ID,
DEVDV_CIB_DVISE_ID,
CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV, CONDV where DEVDV_ID = CONDV_DEVDV_ID
UNION ALL
SELECT DEVDV.ROWID CROWID,
CONDV.ROWID DROW_ID,
DEVDV_ID,
2 AS MARKER,
DEVDV_CIB_DVISE_ID,
DEVDV_SRC_DVISE_ID,
1/CONDV_TX,
CONDV_DATE_DEB,
CONDV_DATE_FIN
FROM
DEVDV, CONDV where DEVDV_ID = CONDV_DEVDV_ID;
truncate table MV_CAPABILITIES_TABLE;
exec dbms_mview.explain_mview('MV_DEVDV_TYP_2');
select capability_name, possible, msgtxt from MV_CAPABILITIES_TABLE;
CAPABILITY_NAME POSSIBLE MSGTXT
PCT N <null>
REFRESH_COMPLETE Y <null>
REFRESH_FAST Y <null>
REWRITE N <null>
PCT_TABLE N relation is not a partitioned table
PCT_TABLE N relation is not a partitioned table
PCT_TABLE N relation is not a partitioned table
PCT_TABLE N relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT Y <null>
REFRESH_FAST_AFTER_ONETAB_DML Y <null>
REFRESH_FAST_AFTER_ANY_DML Y <null>
REFRESH_FAST_PCT N PCT FAST REFRESH is not possible if query has set operand query blocks
REWRITE_FULL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_PARTIAL_TEXT_MATCH N set operator encountered in mv
REWRITE_PARTIAL_TEXT_MATCH N query rewrite is disabled on the materialized view
REWRITE_GENERAL N set operator encountered in mv
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 relation is not a partitioned table
PCT_TABLE_REWRITE N relation is not a partitioned table
PCT_TABLE_REWRITE N relation is not a partitioned table
PCT_TABLE_REWRITE N relation is not a partitioned table