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

BD wrote:
> 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.

Does the table have a materialized view log with rowid ?? Received on Wed Oct 11 2006 - 22:34:37 CDT