RE: Critical Qs on materialized views
- From: "VIVEK_SHARMA" <VIVEK_SHARMA@xxxxxxxxxxx>
- To: <jkstill@xxxxxxxxx>
- Date: Mon, 29 Aug 2005 13:31:04 +0530
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@ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA9IR2> create unique index t_idx on t(x) online;
Index created.
ops$tkyte@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jared Still
Sent: Saturday, August 27, 2005 6:04 AM
To: tim@xxxxxxxxx
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 <tim@xxxxxxxxx> 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).
on 8/26/05 5:17 AM, VIVEK_SHARMA at VIVEK_SHARMA@xxxxxxxxxxx wrote:
>
> Thomas, folks
>
> Is there some way of Avoiding creation of Constraint on Source/Master
> table while using Materlialized view refresh?
> NOTE - Production Database does NOT have any constraints & is of 600 GB
>
> Creation of constraints on Source/Master DB Tables would be very time
> consuming, if attempted.
>
> Thanks indeed for the info.
>
**************** 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***
--
//www.freelists.org/webpage/oracle-l
Other related posts:
