| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fast refresh materialized view is not updateable
Thanks Vadim for the example. I have it working. I added a count for each of
the columns that I am summing. Before, I just had a count(*) at the
beginning.
Mike
>From: Vadim Gorbounov <vgorbounov_at_724.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Fast refresh materialized view is not updateable
>Date: Tue, 16 Apr 2002 15:28:19 -0800
>
>Mike,
>What is your Oracle version? There should be no problem. I see you are
>refreshing 'scd_financial_summary_pit_mv1'
>is trailing "1" a typo or you changed MV name?
>I have no problem with your scenari on 8.1.7.2 on Solaris. Although, I
>removed "enable query rewrite " - it is not enabled on this instance, but
>it shouldn't make a difference.
>
>
>SQL> connect test/test_at_dev
>Connected.
>SQL> create table scd_financial_detail(
> 2 claim_carrier_key varchar2(10),
> 3 eval_date date,
> 4 trn_reserve number);
>
>Table created.
>
>SQL>
>SQL>
>SQL> create materialized view log on scd_financial_detail
> 2 with rowid (claim_carrier_key, eval_date, trn_reserve)
> 3 including new values
> 4 ;
>
>Materialized view log created.
>
>SQL> create materialized view scd_financial_summary_pit_mv
> 2 build immediate
> 3 refresh fast on commit
> 4 with rowid
> 5 AS
> 6 select count(*) n,
> 7 claim_carrier_key,
> 8 eval_date,
> 9 sum(trn_reserve) fin_tot_res,
> 10 count(trn_reserve) cnt_tot_res
> 11 from scd_financial_detail
> 12 group by
> 13 claim_carrier_key,
> 14 eval_date
> 15 /
>
>Materialized view created.
>
>SQL> insert into scd_financial_detail values('1', sysdate, 23);
>
>1 row created.
>
>SQL> commit;
>
>Commit complete.
>
>SQL> select * from scd_financial_summary_pit_mv;
>
> N CLAIM_CARR EVAL_DATE FIN_TOT_RES CNT_TOT_RES
>---------- ---------- --------- ----------- -----------
> 1 1 16-APR-02 23 1
>
>SQL> delete from scd_financial_detail;
>
>1 row deleted.
>
>SQL> commit;
>
>Commit complete.
>
>SQL> select * from scd_financial_summary_pit_mv;
>
>no rows selected
>
>SQL> exec dbms_mview.refresh('scd_financial_summary_pit_mv', 'F');
>
>PL/SQL procedure successfully completed.
>
>SQL>
>
>
>-----Original Message-----
>Sent: Tuesday, April 16, 2002 6:28 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Vadim,
>
>I added the field but it still won't fast refresh. When it is first
>created,
>
>the STALENESS col in dba_mviews = FRESH. When I delete a record in the base
>table, it becomes UNUSABLE. A fast refresh gives me this error:
>
>QUESTT:hcl_data> exec
>dbms_mview.refresh('scd_financial_summary_pit_mv1','F');
>BEGIN dbms_mview.refresh('scd_financial_summary_pit_mv1','F'); END;
>
>*
>ERROR at line 1:
>ORA-12057: materialized view "HCL_DATA"."SCD_FINANCIAL_SUMMARY_PIT_MV1" is
>INVALID and must complete refresh
>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 814
>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 872
>ORA-06512: at "SYS.DBMS_SNAPSHOT", line 852
>ORA-06512: at line 1
>
>Mike
>
>
>
> >From: Vadim Gorbounov <vgorbounov_at_724.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: RE: Fast refresh materialized view is not updateable
> >Date: Tue, 16 Apr 2002 13:53:29 -0800
> >
> > Mike,
> > You missing this field:
> >
> > count(trn_reserve) cnt_tot_res
> >
> > Cheers,
> >
> >Vadim Gorbounov
> >Oracle DBA
> >724 Solutions Inc.
> >Tel:(416)226-2900 ext 5070
> >Email: vgorbounov_at_724.com
> >
> >
> >-----Original Message-----
> >Sent: Tuesday, April 16, 2002 4:14 PM
> >To: Multiple recipients of list ORACLE-L
> >
> >
> >I create a fast refresh materialized view that is a single table
> >aggregation, but dml on the base table does not update the materialized
> >view. In dba_mviews, FAST_REFRESHABLE = DIRLOAD_LIMITEDDML. I think that
>I
> >have satisfied the requirements for a fast refresh MV. I can't see the
> >problem. Here is a stripped down version of the view, that can be
>created,
> >but is not updateable:
> >
> >create materialized view log on scd_financial_detail
> >tablespace &&tbsname
> >with rowid (claim_carrier_key, eval_date, trn_reserve)
> >including new values
> >;
> >
> >create materialized view scd_financial_summary_pit_mv
> > tablespace &&tbsname
> > build immediate
> > refresh fast on commit
> > with rowid
> > enable query rewrite AS
> > select count(*),
> > claim_carrier_key,
> > eval_date,
> > sum(trn_reserve) fin_tot_res
> > from scd_financial_detail
> > group by
> > claim_carrier_key,
> > eval_date
> >/
> >
> >Could anyone please tell me what I'm missing?
> >
> >Thanks,
> >
> >Mike
> >
> >
> >_________________________________________________________________
> >MSN Photos is the easiest way to share and print your photos:
> >http://photos.msn.com/support/worldwide.aspx
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Mike Killough
> > INET: mwkillough_at_hotmail.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Vadim Gorbounov
> > INET: vgorbounov_at_724.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access / Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
>
>
>
>_________________________________________________________________
>MSN Photos is the easiest way to share and print your photos:
>http://photos.msn.com/support/worldwide.aspx
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Mike Killough
> INET: mwkillough_at_hotmail.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Vadim Gorbounov
> INET: vgorbounov_at_724.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing)
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough INET: mwkillough_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Apr 17 2002 - 10:18:58 CDT
 |
 |