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@(protected)> create table t ( x int, y int );
Table created.
ops$tkyte@(protected)> create unique index t_idx on t(x) online;
Index created.
ops$tkyte@(protected)> 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:-
__ ____ ____ ____ ____ ____ ____ ____ __ From: oracle-l-bounce@(protected) [mailto:oracle-l-bounce@(protected)] On Behalf Of Jared Still Sent: Saturday, August 27, 2005 6:04 AM To: tim@(protected) 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@(protected)> 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@(protected) 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*** -- http://www.freelists.org/webpage/oracle-l
