| 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
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).
-- 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
 |
 |