Re: Can I Create Materialized View with REFRESH FAST ON COMMIT & UNION ?

On Feb 18, 2:19 pm, krisl..._at_gmail.com wrote:
> Hi all,
>
> We have requirement to create MV with REFRESH FAST ON COMMIT & UNION.
> After trying with the scripts below, I get error :
>
> ORA-12054: cannot set the ON COMMIT refresh attribute for the
> materialized view
>
> The scripts :
> CREATE TABLE TRANS1 (
> doc_no VARCHAR2(10) PRIMARY KEY,
> docdate DATE,
> product VARCHAR2(6),
> qty NUMBER(6,2),
> doc_type VARCHAR(3)
> )
>
> CREATE TABLE TRANS2 (
> doc_no VARCHAR2(10) PRIMARY KEY,
> docdate DATE,
> product VARCHAR2(6),
> qty NUMBER(6,2),
> doc_type VARCHAR(3)
> )
>
> CREATE MATERIALIZED VIEW LOG ON TRANS1
> TABLESPACE USERS
> WITH PRIMARY KEY
>
> CREATE MATERIALIZED VIEW LOG ON TRANS2
> TABLESPACE USERS
> WITH PRIMARY KEY
>
> CREATE MATERIALIZED VIEW MV_TRANS
> TABLESPACE users
> REFRESH FAST ON COMMIT
> WITH PRIMARY KEY
> AS
> SELECT doc_no, qty FROM TRANS1
> UNION ALL
> SELECT doc_no, qty FROM TRANS2
>
> Is there any solution to this problem ?
>
> Thank you for your help,
> xtanto

SQL> create materialized view log on trans1 WITH ROWID; Materialized view log created.

SQL> create materialized view log on trans2 WITH ROWID; Materialized view log created.

SQL> create materialized view mv_trans
  2 refresh fast on commit
  3 as
  4 select ROWID RID, doc_no, qty, 1 QMARK from trans1   5 union all
  6 select ROWID RID, doc_no, qty, 2 QMARK from trans2   7 /

Materialized view created

SQL> insert into trans1 values('abc',sysdate,'book',1,'x');

1 row inserted

SQL> insert into trans2 values('abc',sysdate,'book',1,'x');

1 row inserted

SQL> insert into trans1 values('abc',sysdate,'book',1,'x');

insert into trans1 values('abc',sysdate,'book',1,'x')

ORA-00001: unique constraint (BOBZ.SYS_C003435) violated

SQL> insert into trans1 values('abcD',sysdate,'book',1,'x');

1 row inserted

SQL> commit;

Commit complete

SQL> select doc_no, qty from mv_trans;

DOC_NO QTY
---------- --------

abc            1,00
abc            1,00
abcD           1,00

You should include ROWIDs in MV logs AND in the MV query for join MVs (and UNION is a join of sorts.) Also note the QMARK column included in each query block in UNION ALL - this is the documented requirement for a UNION ALL-based MV to be fast-refreshable, otherwise Oracle will be unable to distinguish rows coming from united tables.

The above test was executed on Oracle 10.2.0.3, but should work on 9.2 and 10.1 as well.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Feb 20 2008 - 04:13:46 CST