Re: MV creation error

From: Vladimir Begun <Vladimir.Begun_at_oracle.com>
Date: Thu, 10 Feb 2005 10:52:24 -0800
Message-ID: <420BAD68.6070907@oracle.com>

> Could someone please let me know why this is happening and how to
> overcome this issue?

WITH PRIMARY KEY Clause

Specify WITH PRIMARY KEY to create a primary key materialized view. This is the default and should be used in all cases except those described for WITH ROWID. Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view

for fast refresh. The master table must contain an enabled primary key constraint,
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
and the defining query of the materialized view must specify all of the primary
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
key columns directly. That is, in the defining query, the primary key columns
^^^^^^^^^^^^^^^^^^^^

cannot be specified as the argument to a function such as UPPER.

# All tables in the materialized view must have materialized view logs, and the materialized view logs must:

Contain all columns from the table referenced in the materialized view.
Specify with ROWID and INCLUDING NEW VALUES.
Specify the SEQUENCE clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.

DROP TABLE t2;
CREATE TABLE t2 AS SELECT owner, object_name, object_id, data_object_id, object_type, created FROM all_objects WHERE 1 = 2; ALTER TABLE t2 ADD CONSTRAINT pk$t2 PRIMARY KEY(object_id); CREATE MATERIALIZED VIEW LOG ON t2 WITH ROWID, SEQUENCE (object_type) INCLUDING NEW VALUES; DROP MATERIALIZED VIEW mv2;
create materialized view mv2 refresh fast as select object_type, count(*) from t2 group by object_type /

-- 
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 10 2005 - 13:56:37 CST