ORA-12015:无法通过复杂的查询创建快速刷新的物化视图

问题描述：

我正在使用以下查询来构建实例化视图.

I am using below query to build a materialized view.

CREATE MATERIALIZED VIEW gcms_business_profile_mview 
BUILD IMMEDIATE  
REFRESH FAST  
        WITH PRIMARY KEY  
        START WITH SYSDATE  
        NEXT (TRUNC (SYSDATE + 1) + 20 / 96)  
AS
SELECT DISTINCT obp.bp_id,
       obp.bp_typ_cd,
       os.spcl_desc,
       obpi.frs_nm,
       obpi.mdl_nm,
       NVL (rep_lst_nm.lst_nm, othr_lst_nm.lst_nm) last_name,
       NVL (rep_lst_nm.lst_nm_typ_id, othr_lst_nm.lst_nm_typ_id)
          last_name_type_id       
  FROM tr_ods.ods_business_parties obp
       LEFT JOIN (  SELECT bp_id,  
                                speciality_id,  
                                updtd_dt,  
                                ROW_NUMBER ()  
                                OVER (PARTITION BY bp_id ORDER BY updtd_dt DESC)  
                                   AS spec_rn  
                           FROM tr_ods.ods_bp_specialty  
                          WHERE updtd_dt IS NOT NULL  
                       ) obs  
               ON obs.bp_id = obp.bp_id  
               AND obs.spec_rn =1 
       LEFT JOIN tr_ods.ods_specialty  os                                       
          ON  os.speciality_id = latest_spec.speciality_id 
          AND    os.delete_flag = 'N'
       LEFT JOIN tr_ods.ods_business_party_individuals obpi   
          ON obpi.bp_id = obp.bp_id
       LEFT JOIN (SELECT obpln1.bp_id,
                obpln1.lst_nm,
                obpln1.lst_nm_typ_id,
                ROW_NUMBER ()
                   OVER (PARTITION BY obpln1.bp_id ORDER BY updtd_dt DESC)
                   AS lst_rn_22
           FROM tr_ods.ods_business_party_last_names obpln1
          WHERE     lst_nm_typ_id = 22
                AND updtd_dt =
                       (SELECT MAX (obpln2.updtd_dt)
                          FROM tr_ods.ods_business_party_last_names obpln2
                         WHERE     obpln2.bp_id = obpln1.bp_id
                               AND obpln2.lst_nm_typ_id = 22)) rep_lst_nm                             
          ON (rep_lst_nm.bp_id = obp.bp_id AND rep_lst_nm.lst_rn_22 = 1)
       LEFT JOIN (SELECT obpln1.bp_id,
                obpln1.lst_nm,
                obpln1.lst_nm_typ_id,
                ROW_NUMBER ()
                   OVER (PARTITION BY obpln1.bp_id ORDER BY updtd_dt DESC)
                   AS lst_rn
           FROM tr_ods.ods_business_party_last_names obpln1
          WHERE     lst_nm_typ_id IN (21, 23)
                AND updtd_dt =
                       (SELECT MAX (obpln2.updtd_dt)
                          FROM tr_ods.ods_business_party_last_names obpln2
                         WHERE     obpln2.bp_id = obpln1.bp_id
                               AND obpln2.lst_nm_typ_id IN (21, 23))) othr_lst_nm                                  
          ON (othr_lst_nm.bp_id = obp.bp_id AND othr_lst_nm.lst_rn = 1)

我得到

ORA-12015:无法根据复杂的查询错误消息创建快速刷新的物化视图.

ORA-12015: cannot create a fast refresh materialized view from a complex query error message.

我已经为所有表创建了物化视图日志.有人可以帮我解决此问题吗?

I have already created materialized view logs for all the tables. Could any one please help me in fixing this issue?

答

创建材料视图-快速刷新的限制

FAST刷新的限制

快速刷新受以下限制:

在创建时指定FAST刷新时，Oracle数据库会验证您正在创建的实例化视图可以快速进行刷新.当您在ALTER中将刷新方法更改为FAST时 MATERIALIZED VIEW语句，Oracle数据库不执行此操作确认.如果物化视图不适合快速查看刷新，然后当您尝试执行以下操作时，Oracle数据库将返回错误: 刷新此视图.

When you specify FAST refresh at create time, Oracle Database verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to FAST in an ALTER MATERIALIZED VIEW statement, Oracle Database does not perform this verification. If the materialized view is not eligible for fast refresh, then Oracle Database returns an error when you attempt to refresh this view.

材料化视图，则不能进行快速刷新查询包含解析函数或XMLTable函数.

Materialized views are not eligible for fast refresh if the defining query contains an analytic function or the XMLTable function.

材料化视图，则不能进行快速刷新查询引用了在其上定义了XMLIndex索引的表.

Materialized views are not eligible for fast refresh if the defining query references a table on which an XMLIndex index is defined.

如果物化视图的任何列为加密.

You cannot fast refresh a materialized view if any of its columns is encrypted.

您的查询包含分析功能:

Your query contains an analytic finction:

ROW_NUMBER () OVER (PARTITION BY bp_id ORDER BY updtd_dt DESC)

因此，您无法对该查询使用快速刷新.

therefore you cannot use a fast refresh for this query.

ORA-12015:无法通过复杂的查询创建快速刷新的物化视图

相关推荐