Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Fast refresh materialized view is not updateable

RE: Fast refresh materialized view is not updateable

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Tue, 16 Apr 2002 16:18:21 -0800
Message-ID: <F001.00445EC5.20020416161821@fatcity.com>


I have no problem doing this Oracle 8.1.7.3 on Solaris 2.6:

SQL> create table testmat ( c1 number , c2 number , c3 number);

Table created.

SQL> insert into testmat values ( 1 , 2 , 10);

1 row created.

SQL> insert into testmat values ( 1 , 3 , 13);

1 row created.

SQL> insert into testmat values ( 2 , 2 , 5);

1 row created.

SQL> insert into testmat values ( 1 , 2 , 6);

1 row created.

SQL> insert into testmat values ( 2 , 2 , 10);

1 row created.

SQL>
SQL> select count(*),c1,c2,sum(c3)
  2 from testmat
  3 group by c1,c2;

  COUNT(*) C1 C2 SUM(C3) ---------- ---------- ---------- ----------

         2          1          2         16
         1          1          3         13
         2          2          2         15

SQL>
SQL> create materialized view log on testmat   2 with rowid (c1,c2,c3)
  3 including new values;

Materialized view log created.

SQL>

SQL> create materialized view  testmatagg      
  2         build immediate
  3         refresh fast on commit with rowid
  4           enable query rewrite AS
  5            select count(*) countstar,c1,c2,sum(c3) sum_c3,count(c3)
count_c3
  6              from  testmat
  7              group by c1,c2;

Materialized view created.

SQL>
SQL> select * from testmatagg;

 COUNTSTAR C1 C2 SUM_C3 COUNT_C3 ---------- ---------- ---------- ---------- ----------

         2          1          2         16          2
         1          1          3         13          1
         2          2          2         15          2

SQL>
SQL> insert into testmat values ( 2 , 2 , 10);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*),c1,c2,sum(c3)
  2 from testmat
  3 group by c1,c2;

  COUNT(*) C1 C2 SUM(C3) ---------- ---------- ---------- ----------

         2          1          2         16
         1          1          3         13
         3          2          2         25

SQL>
SQL> select * from testmatagg;

 COUNTSTAR C1 C2 SUM_C3 COUNT_C3 ---------- ---------- ---------- ---------- ----------

         2          1          2         16          2
         1          1          3         13          1
         3          2          2         25          3

SQL> Regards,

Waleed

-----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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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 Tue Apr 16 2002 - 19:18:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US