Oracle 具有内部连接的联合 (或所有联合) 的物化视图

你好，

对于性能需要，我想使用以下脚本在提交刷新选项上创建一个实体化视图:

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说这是一个复杂的查询，不符合快速刷新的要求，

你能告诉我我打破了哪条规则吗？

谢谢

如果您正在努力进行MV刷新，那么最好的方法是使用DBMS_MVIEW.EXPLAIN_MVIEW过程。

这告诉你为什么各种刷新是不可能的:

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                                                                                           
REFRESH_COMPLETE                 Y                                                                                           
REFRESH_FAST                     N                                                                                           
REWRITE                          N                                                                                           
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    
复制

并查看文档中的刷新限制https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/basic-materialized-views.html#GUID-505C24CF-5D56-4820-88AA-2221410950E7

这不是特别明显。但是ANSI的加入和具体化的观点仍然存在一些问题。

转换为甲骨文风格，一切都应该很好:

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                                                                                           
REFRESH_COMPLETE                 Y                                                                                           
REFRESH_FAST                     Y                                                                                           
REWRITE                          N                                                                                           
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                                                                                           
REFRESH_FAST_AFTER_ONETAB_DML    Y                                                                                           
REFRESH_FAST_AFTER_ANY_DML       Y                                                                                           
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
复制