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

[kris...@gmail.com]

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

[Vladimir M. Zakharychev]

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