| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Critical Qs on materialized views
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:-
Thanks indeed
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_at_evdbt.com> 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_at_infosys.com 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.
>
 |
 |