Re: Can I Create Materialized View with REFRESH FAST ON COMMIT & UNION ?
- From: "Vladimir M. Zakharychev" <vladimir.zakharychev@xxxxxxxxx>
- Date: Wed, 20 Feb 2008 02:13:46 -0800 (PST)
On Feb 18, 2:19 pm, krisl...@xxxxxxxxx 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
.
- References:
- Prev by Date: Re: How to check database resource limit setup?
- Next by Date: isqlplus, calling stored procedure with cursor
- Previous by thread: Can I Create Materialized View with REFRESH FAST ON COMMIT & UNION ?
- Next by thread: Problem installing Oracle 11g on Windows XP Professional SP2
- Index(es):
Relevant Pages
|
