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

Background:

I have a task wherein I must replicate tables from a remote database using materialized views.

9.2.0.6 on AIX 5.2.0.2.

I am currently refreshing this MV with the FORCE switch, and using WITH ROWID. I am finding that all refreshes are being done as COMPLETE, instead of FAST as I would like to see. This is particularly important in the case of one MV which is 67 million rows.

I have been told that in the case of this large one, the master table snapshot is using PK.

I am reading on TAHITI about this, and find what I see as a contradiction in the documentation.

Specifically, I'm reading
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87499/repmview.htm#25269

The documentation says the following:

"A materialized view can be either read-only, updatable, or writeable. For read-only, updatable, and writeable materialized views, the defining query of the materialized view must reference all of the primary key columns in the master."

((I assume here that "defining query" means the CREATE MATERIALIZED VIEW statement. I read this to mean that for *all* types of MV, the defining query must reference all PK values))

And a bit further down, they provide an example of a Primary Key view, but this create statement does NOT include the primary key columns of the master.

The example given is CREATE MATERIALIZED VIEW oe.customers FOR UPDATE AS
  SELECT * FROM oe.customers_at_orc1.world;

And my attempt to create an MV based on Primary key fails when I specify the PK values. My create statement is:

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"

This create statement worked fine using "WITH ROWID". - with the exception that all refreshes were COMPLETE instead of FAST, which I'm trying to resolve.

*However* - if I simply say "WITH PRIMARY KEY AS SELECT", and *not* include the PK value, the MV is created successfully, with the same primary key as the master table.

So the documentation confuses me here, as it explicitly says that PK values must be specified - where in reality, attempts to do so fail, and attempts without that specification seem perfectly fine.

It also appears that I don't even *need* to say "WITH PRIMARY KEY AS", since the PK materialized view is the default type anyway.

I am perfectly happy to try all these variants and settle on one that works, but if someone could point out my misunderstanding here, I'd be grateful.

Cheers,

BD. Received on Wed Oct 11 2006 - 12:05:09 CDT