Re: Can someone clarify something on TAHITI for me? - re MVs

On Wed, 11 Oct 2006 10:05:09 -0700, BD wrote:

> CREATE MATERIALIZED VIEW TEST_MV
> TABLESPACE TS1
> BUILD IMMEDIATE
> USING INDEX
> TABLESPACE TS2
> REFRESH FORCE ON DEMAND WITH PRIMARY KEY (PK_VALUE) AS
> SELECT * FROM SCHEMA.MASTER_TABLE_at_DBLINK
> NOLOGGING;
>
> The error asterisk is right at the opening paren of PK_VALUE, and the
> error is "ERROR at line 1:
> ORA-00905: missing keyword"

You don't need "(PK_VALUE)". What you're trying to do is to tell oracle to use primary key instead of rowid during refresh. Oracle will be quite capable of finding out about the columns that comprise the primary key. This is your SQL:
CREATE MATERIALIZED VIEW TEST_MV

 	TABLESPACE TS1
 	BUILD IMMEDIATE
 	USING INDEX
 		TABLESPACE TS2
 	REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS
 		SELECT * FROM SCHEMA.MASTER_TABLE_at_DBLINK
 	NOLOGGING;

-- 
http://www.mladen-gogala.com