[Oracle-l] Critical Qs on materialized views

We need to create the Materlialized view on PRE-Built Table WITHOUT
adding a Constraint to the Master/source Table. This is needed because
the Production (LIVE) Database(Master/Source) has NO existing
constraints & creating the same on the large 600 GB production
Database's tables would be nearly impossible.

NOTE - We are getting the following Errors when attempting to create a
Materlialized view on PRE-Built Table WITHOUT adding a Constraint to the

Master/source Table:-

SQL> create materialized view d1 BUILD IMMEDIATE
2 refresh complete as select * from d1_at_S829mig_teja;
refresh complete as select * from d1_at_S829mig_teja
*
ERROR at line 2:
ORA-12014: table 'D1' does not contain a primary key constraint
SQL> create materialized view d1 BUILD IMMEDIATE refresh complete with
rowid
2 as select * from d1_at_S829mig_teja;
as select * from d1_at_S829mig_teja
*
ERROR at line 2:
ORA-00955: name is already used by an existing object
SQL> create materialized view d1 on prebuilt table refresh complete with
rowid
2 as select * from d1_at_S829mig_teja;
as select * from d1_at_S829mig_teja
*
ERROR at line 2:
ORA-12058: materialized view cannot use prebuilt table

Give Command with respective syntax, if possible.

Another basic question - When a constraint is created does it
automatically create an internal underlying index on the respective
table?

Thanks indeed

CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

ENFORCED constraints are used by MV refresh only for the purpose of guarding
against "staleness" as specified by the default setting of the parameter
QUERY_REWRITE_INTEGRITY to the value of "ENFORCED".

Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED" (i.e.
Functionality disabled) will do what you want. Just be sure to understand
the implications of this setting (i.e. DBA becomes responsible for ensuring
that MV is never "stale" for query rewrite).

Hi Tim,

Interesting, I wish I had known that last week. :)

I'm in the midst of rebuilding a rather large table to claim
back ~200g of space from it.

The table in question has a unique index, but no PK.

The workaround was easy: add a primary key constraint to the table,
a 2 second operation with a unique index in place.

I think it would be interesting to rerun the prototype though without
the PK and with QUERY_REWRITE_INTEGRITY = STALE_TOLERATED

Thanks,

Jared

When you create your materialized view use the "with rowid" phrase to base
fast refreshes off of the master table's rowids rather than its primary key
(which you've already stated doesn't exist):

create materialized view mv
refresh force with rowid as
select * from master_table;

Of course, to do fast refreshes you'll still need to create materialized
view logs on the master tables to record changes. You'll need to specify
"with rowid" when you create these as well:

create materialized view log on master_table with rowid;

I hope this is what you were asking for.
Jason Heinrich
Oracle Database Administrator
Pensacola Christian College
(850) 478-8496 x2509
jheinrich_at_pcci.edu

You cannot use 'WITH ROWID' on a MV based on a prebuilt table.

The prebuilt table requirement was stated in a followup email.

Jared

Jared, Folks

Qs 1 Can the PK constraint be created on a PRE-Created (already existing) Unique index in ver 8i too?

Qs 2 Like an index can be created in parallel with NOLOGGING, ONLINE Clauses can the Primary Key Constraint also be created with such clauses on a Table having NO indexes? If NOT then would it NOT be advantageous to 1st Precreate the unqiue index & then add the PK constraint?

ops$tkyte_at_ORA9IR2> create table t ( x int, y int );

Table created.

ops$tkyte_at_ORA9IR2> create unique index t_idx on t(x) online;

Index created.

ops$tkyte_at_ORA9IR2> alter table t add constraint t_pk primary key(x) rely
novalidate;

Qs 3 What is the significance of the above "rely novalidate" clauses?

Taken from the following GOOD Link on PK vs index:-

http://asktom.oracle.com/pls/ask/f?p=4950:8:15663747642085018977::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:4941517587762

Thanks indeed

From: oracle-l-bounce_at_freelists.org On Behalf Of Jared Still
Sent: Saturday, August 27, 2005 6:04 AM
To: tim_at_evdbt.com
Cc: Oracle-L
Subject: Re: Critical Qs on materialized views

Hi Tim,

Interesting, I wish I had known that last week. :)

I'm in the midst of rebuilding a rather large table to claim
back ~200g of space from it.

The table in question has a unique index, but no PK.

The workaround was easy: add a primary key constraint to the table,
a 2 second operation with a unique index in place.

I think it would be interesting to rerun the prototype though without
the PK and with QUERY_REWRITE_INTEGRITY = STALE_TOLERATED

Thanks,

Jared
On 8/26/05, Tim Gorman wrote:
ENFORCED constraints are used by MV refresh only for the purpose of guarding
against "staleness" as specified by the default setting of the parameter
QUERY_REWRITE_INTEGRITY to the value of "ENFORCED".

Setting QUERY_REWRITE_INTEGRITY to the value of "STALE_TOLERATED" (i.e.
Functionality disabled) will do what you want.  Just be sure to understand
the implications of this setting (i.e. DBA becomes responsible for ensuring
that MV is never "stale" for query rewrite).
CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

comments inline
Check the docs. I don't think this option existed in 8i. You won't know for
sure until you check the docs
and/or try it.

Qs 2 Like an index can be created in parallel with NOLOGGING, ONLINE Clauses
You can build a PK by that method, but it doesn't seem to work for MV's.

Here's the scenario I've been working with. I need to rebuild a rather large
table with little or no downtime.

The chosen method is via MV.

Updates to the source table must be preserved so that they can later be
applied to the MV table.

You *could* pre-create the table, then create the PK, and finally create
the MV on it with pre-built table.

When you do this though, the rows in MLOG$_ will
be deleted without being applied to the MV. Correcting this will require a
complete
refresh, which will take as long as building the table in the first place.

The following test demonstrates that:

12:41:33 SQL>create table m1( pk number(4) not null, name varchar2(20) not
null);

Table created.

12:41:33 SQL>

12:41:33 SQL>create index m1_idx on m1(pk) nologging;

Index created.

12:41:33 SQL>alter table m1 add constraint m1_pk primary key(pk);

Table altered.

12:41:33 SQL>

12:41:33 SQL>insert into m1 values(1,'Row 1');

1 row created.

12:41:33 SQL>commit;

Commit complete.

12:41:33 SQL>
12:41:33 SQL>create table m2
12:41:33 2 nologging
12:41:33 3 as
12:41:33 4 select *
12:41:33 5 from m1
12:41:33 6 /

Table created.

12:41:33 SQL>

12:41:33 SQL>create index m2_idx on m2(pk) nologging;

Index created.

12:41:33 SQL>alter table m2 add constraint m2_pk primary key(pk);

Table altered.

12:41:33 SQL>

12:41:33 SQL>create materialized view log on m1;

Materialized view log created.

12:41:33 SQL>

12:41:33 SQL>insert into m1 values(2,'Row 2');

1 row created.

12:41:33 SQL>commit;

Commit complete.

12:41:33 SQL>
12:41:33 SQL>
12:41:33 SQL>create materialized view m2
12:41:33 2 on prebuilt table
12:41:33 3 refresh fast
12:41:33 4 as
12:41:33 5 select *
12:41:33 6 from m1
12:41:33 7 /

Materialized view created.

12:41:33 SQL>

12:41:33 SQL>exec dbms_mview.refresh('M2','FAST')

PL/SQL procedure successfully completed.

12:41:33 SQL>
12:41:33 SQL>
12:41:33 SQL>select * from m1;

PK NAME

---------- --------------------

1 Row 1
2 Row 2

2 rows selected.

12:41:33 SQL>select * from m2;

PK NAME

---------- --------------------

1 Row 1

1 row selected.

12:41:33 SQL>

12:41:33 SQL>select * from mlog$_m1;

no rows selected

12:41:33 SQL>

12:41:33 SQL>set echo off

I've found that something similar will happen if you create the materialized
view with 'never refresh', alter it
to 'refresh fast' and then try to do a fast refresh. The difference is an
error is generated.

12:46:30 SQL>create materialized view m2
12:46:30 2 on prebuilt table
12:46:30 3 never refresh
12:46:30 4 as
12:46:30 5 select *
12:46:30 6 from m1
12:46:30 7 /

Materialized view created.

12:46:30 SQL>

12:46:30 SQL>alter materialized view m2 refresh fast;

Materialized view altered.

12:46:30 SQL>

12:46:30 SQL>exec dbms_mview.refresh('M2','FAST')
BEGIN dbms_mview.refresh('M2','FAST'); END;

*
ERROR at line 1:
ORA-12057: materialized view "JS001292"."M2" is INVALID and must complete
refresh

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1

In both cases a complete refresh is required.

To work with what MV creation will allow (at least to the best of my
knowledge at this time)
the target table is created empty with a primary key constraint.

A complete refresh is then done, and the MV altered to do periodic fast
refreshes.

Other indexes are then created as needed.

I haven't yet tried any of Tim's suggestions, but may if I have time.

D'oh!

Of course it works. I misunderstood that question. Reading too
fast, too much going on.

I saw 'PRE' and thought he was still talking about PREbuilt table.

That has worked for as long as I can recall, sometime in Version 7, maybe
from the beginning.

Jared
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist