How to Use a UNION Marker To Make a UNION ALL Mview Fast Refreshable (Doc ID 786403.1)

Last updated on AUGUST 04, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 10.2.0.1 - Release: 9.2 to 10.2
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.2.0.8

Goal

You have a materialized view that contains a UNION ALL statement, and you are unable to create it as a REFRESH FAST materialized view.   You have done the following:

1. Run dbms_mview.explain_mview and found that the mview is not refreshable because of the following reason (see note references at the end of this document if you need instructions for running this utility):

CAPABILITY_NAME                P MSGTXT 
------------------------------ - -------------------------------------------------------------------------------- 
REFRESH_COMPLETE              Y 
REFRESH_FAST                  N 
REFRESH_FAST_AFTER_INSERT     N the materialized view does not have a UNION  ALL marker column 
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 is not possible on any of the detail tables in the materialized view

2. Reveiwed the section titled "Restrictions on Fast Refresh on Materialized Views With the UNION ALL Operator" in the Oracle9i Data Warehousing Guide and have made sure your DDL meets all the restrictions except for the need to place a UNION ALL marker column in each SELECT list.

3.  Reviewed <> "How to Create a Fast Refreshed Materialized View Containing UNION ALL Set Operation," but are unable to make the marker work because the FROM clause in your DDL has more than a single table above and below the UNION ALL statement.

Here is an example of an materialized view DDL that contains two tables in the FROM statement above the UNION ALL and three tables in the FROM statement below the UNION ALL.

CREATE MATERIALIZED VIEW WEBTRANSPORT_MV 
REFRESH FAST
AS
SELECT T.INVTRANSP_TRANSP_TRANSP_ID,
LPAD(TO_CHAR( T.ACCT_ACCT_NUM), 8,'00000000') acct_acct_num,
T.TRANSPSTAT_TRANSP_STATUS_CODE,
T.TRANSPTYPE_TRANSP_TYPE_CODE,
LPAD( TO_CHAR( T.LOW_BAL_AMT * 100),7,'0000000') BAL_AMT,
LPAD(TO_CHAR( T.REPLENISH_AMT * 100),7,'0000000' ) REPL_AMT,
TO_CHAR(T.ISSUE_DATE,'MM/DD/YYYY') issue_date,
TO_CHAR(T.SALE_DATE,'MM/DD/YYYY') sale_date,
T.APPRKG_FLAG OPT_OUT_FLAG,
LPAD(TO_CHAR(T.WEB_REF_SEQ),8,'00000000') web_ref_seq
FROM PA_ACCT_TRANSP T, PA_ACCT A                         <--- 2 tables
WHERE A.ACCTTYPE_ACCT_TYPE_CODE in ('01','03','04')
AND A.ACCTSTAT_ACCT_STATUS_CODE in ('01','02','03','04')
AND A.ACCT_NUM = T.ACCT_ACCT_NUM
AND T.TRANSPSTAT_TRANSP_STATUS_CODE IN ('01','02','04','05')
AND NVL(T.TRANSP_SETTLED_CLOSED,'N') != 'Y'
union all
SELECT T.INVTRANSP_TRANSP_TRANSP_ID,
LPAD(TO_CHAR( T.ACCT_ACCT_NUM), 8,'00000000') acct_acct_num,
T.TRANSPSTAT_TRANSP_STATUS_CODE,
T.TRANSPTYPE_TRANSP_TYPE_CODE,
LPAD( TO_CHAR( C.COMM_ACCT_THRESH_AMT * 100),7,'0000000') BAL_AMT,
LPAD(TO_CHAR( C.COMM_ACCT_REPL_AMT * 100),7,'0000000' ) REPL_AMT,
TO_CHAR(T.ISSUE_DATE,'MM/DD/YYYY') issue_date,
TO_CHAR(T.SALE_DATE,'MM/DD/YYYY') sale_date,
T.APPRKG_FLAG OPT_OUT_FLAG,
LPAD(TO_CHAR(T.WEB_REF_SEQ),8,'00000000') web_ref_seq
FROM PA_ACCT_TRANSP T, PA_ACCT A, PA_COMMERCIAL C        <--- 3 tables
WHERE A.ACCTTYPE_ACCT_TYPE_CODE = '02'
AND A.ACCTSTAT_ACCT_STATUS_CODE in ('01','02','03','04')
AND A.ACCT_NUM = T.ACCT_ACCT_NUM
AND A.ACCT_NUM = C.ACCT_ACCT_NUM
AND T.TRANSPSTAT_TRANSP_STATUS_CODE IN ('01','02','04','05')
AND NVL(T.TRANSP_SETTLED_CLOSED,'N') != 'Y'
/

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms