Fast refreshable materialized view

Hi!

I have a problem to create a fast refreshable materialized view with a join of a self referencing table:

DROP TABLE t1;
CREATE TABLE t1(

	c11	INTEGER NOT NULL,
	c12	INTEGER );

ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY( c11 );

DROP TABLE t2;
CREATE TABLE t2(

	c21	INTEGER NOT NULL,
	c22	INTEGER );

ALTER TABLE t2 ADD CONSTRAINT pk_t2 PRIMARY KEY( c21 );

CREATE MATERIALIZED VIEW LOG ON t1 WITH ROWID; CREATE MATERIALIZED VIEW LOG ON t2 WITH ROWID;

DROP MATERIALIZED VIEW mv12;
CREATE MATERIALIZED VIEW mv12

	BUILD DEFERRED
	REFRESH FAST ON DEMAND
	ENABLE QUERY REWRITE
AS
	SELECT	tab1.*, tab2.*,
		tab1.rowid t1_rowid, tab2.rowid t2_rowid
	FROM	t1 tab1, t2 tab2
	WHERE	tab1.c11 = tab2.c21;

DROP MATERIALIZED VIEW mv11;
CREATE MATERIALIZED VIEW mv11

	BUILD DEFERRED
	REFRESH FAST ON DEMAND
	ENABLE QUERY REWRITE
AS
	SELECT	tab1.*, tab2.*,
		tab1.rowid t1_rowid, tab2.rowid t2_rowid
	FROM	t1 tab1, t1 tab2
	WHERE	tab1.c11 = tab2.c12;

mv12 is created very well, mv11 brings the error message:

	FROM t1 tab1, t1 tab2
	     *
	ERROR in Line 8:
	ORA-12015: cannot create a fast refresh snapshot from a complex query

The only difference between these MV's is that mw11 is self referencing table 't1'.

Thanks for any help.

        Volker Received on Thu Aug 30 2001 - 14:36:31 CDT