Re: Problem With Fast Refresh Of Materialized View

Thanks.

On Mon, Jun 8, 2015 at 2:45 PM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:

> Hi David
>
> The column list in parentheses in the beginning of your CREATE TABLE does
> not include the three ROWID's.
>
> Either include three ROWID's (rowid1, rowid2, rowid3) in that column list.
> Or just remove the column list - then columns will be autonamed from the
> columns/aliases of the query.
>
>
>
> Regards
>
>
> Kim Berg Hansen
>
> http://dspsd.blogspot.com
> kibeha_at_gmail.com
> _at_kibeha
>
>
> On Mon, Jun 8, 2015 at 9: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