Re: MV creation error
- From: Vladimir Begun <Vladimir.Begun@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Feb 2005 10:52:24 -0800
> 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
