Re: Problem With Fast Refresh Of Materialized View

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Mon, 8 Jun 2015 15:46:29 -0400
Message-ID: <CAAaXtLB3kp+pCR3qZCHVWHOwnvu4WNoGB37SD0raWW5Kk9gFhw_at_mail.gmail.com>

I think that the problem is that the column list... ("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR", "VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO", "ROOT")
Does not include the 3 ROWID columns you added to the SELECT LIST.

On Mon, Jun 8, 2015 at 3:32 PM, David Barbour <david.barbour1_at_gmail.com> wrote:

> Oracle 11.2.0.3 RHEL 6.3
>
> Trying to create a materialized view with fast refresh capabilities.
> Created materialized view logs on the base tables with rowid.
>
> CREATE MATERIALIZED VIEW "SAPR3"."V_IBINVALUES_F"
> ("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR",
> "VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO",
> "ROOT")
> TABLESPACE PSAPBTABD
> BUILD IMMEDIATE
> AS
> SELECT T1."MANDT", T1."SYMBOL_ID", T1."ATZIS", T1."ASSTYP", T1."IN_RECNO",
> T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
> T3."VALFR", T3."VALTO", T3."ROOT"
> FROM "IBINVALUES" T1, "IBIN" T2, "IBST" T3
> WHERE T2."MANDT" = T1."MANDT"
> AND T2."IN_RECNO" = T1."IN_RECNO"
> AND T3."MANDT" = T1."MANDT"
> AND T3."INSTANCE" = T2."INSTANCE"
> AND ( T1."ASSTYP" = ' ' OR T1."ASSTYP" = 'F' )
> /
> Materialized view created.
>
> Then check the requirements against mv_capabilities:
>
>
> SQL> exec dbms_mview.explain_mview(mv=>'V_IBINVALUES_F',stmt_id=>'100');
>
> PL/SQL procedure successfully completed.
>
> SQL> commit;
>
> Commit complete.
>
> 1 select CAPABILITY_NAME, POSSIBLE, MSGTXT, RELATED_TEXT from
> mv_capabilities_table
> 2 where capability_name like 'REFRESH%'
> 3* and capability_name not like '%PCT%'
> SQL> /
>
> CAPABILITY_NAME P MSGTXT
> RELATED_TEXT
> ------------------------------ -
> ----------------------------------------------------------------------
> ----------------------------------------
> REFRESH_COMPLETE Y
> REFRESH_FAST N
> REFRESH_FAST_AFTER_INSERT N the SELECT list does not have the rowids
> of all the detail tables T3
> 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
>
> Okay - so I include the rowids - or at least I try:
>
> SQL> drop materialized view V_IBINVALUES_F;
>
> Materialized view dropped.
>
> SQL> CREATE MATERIALIZED VIEW "SAPR3"."V_IBINVALUES_F"
> ("MANDT", "SYMBOL_ID", "ATZIS", "ASSTYP", "IN_RECNO", "INSTANCE", "VALFR",
> "VALTO", "OBJECTTYP", "OBJNR", "STATUS", "CUCOCNT", "ST_VALFR", "ST_VALTO",
> "ROOT")
> 2 3 TABLESPACE PSAPBTABD
> 4 BUILD IMMEDIATE
> 5 AS
> 6 SELECT
> 7 T1.ROWID, T2.ROWID, T3.ROWID,
> 8 T1."MANDT", T1."SYMBOL_ID", T1."ATZIS", T1."ASSTYP", T1."IN_RECNO",
> 9 T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
> 10 T3."VALFR", T3."VALTO", T3."ROOT"
> FROM "IBINVALUES" T1, "IBIN" T2, "IBST" T3
> 11 12 WHERE T2."MANDT" = T1."MANDT"
> 13 AND T2."IN_RECNO" = T1."IN_RECNO"
> 14 AND T3."MANDT" = T1."MANDT"
> 15 AND T3."INSTANCE" = T2."INSTANCE"
> 16 AND ( T1."ASSTYP" = ' ' OR T1."ASSTYP" = 'F' )
> 17 /
> T2."INSTANCE", T2."VALFR", T2."VALTO", T2."OBJECTTYP", T2."OBJNR",
> T2."CSTATUS", T2."CUCOCNT",
>
> *
> ERROR at line 9:
> ORA-01730: invalid number of column names specified
>
> The little asterisk is under the T2.CUCOCNT selection.
>
> It's Monday. What am I missing here?
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 08 2015 - 21:46:29 CEST