Re: disabling and enabling all constraints

The paragraph right before the paragraph that contained "the $400 RevealNet product is not 100%" stated that we need to look at four dictionary views to accurately regenerate constraints: DBA_CONSTRAINTS, DBA_INDEXES, DBA_CONS_COLUMNS, DBA_IND_COLUMNS The RevealNet product only looks at three of them and doesn't look at DBA_IND_COLUMNS. To RevealNet's credit, their constraint script is more complete than any of the others I've seen.

As mentioned in my previous message, if you don't query DBA_IND_COLUMNS, you can't match up the constraints that don't have the same name as their underlying index.

Here's an example of this:

 create table contact_t
 (
   contact_id number(7),
   fname varchar(30)
 )
 tablespace ts_db1
 storage (initial 128K next 128K minextents 1 pctincrease 0);

 create unique index XYZ on contact_t(contact_id)   tablespace ts_idx1
  storage (initial 128K next 128K minextents 1 pctincrease 0);

 alter table contact_t
   add constraint cntct_contact_id_pk primary key (contact_id);

Since the free-standing "XYZ" index already satisfies the requirements to support the index, the "alter table" SQL statement silently "marries" the constraint "cntct_contact_id_pk" to the "XYZ" index. Had the "XYZ" index not existed, the "alter table" statement would have created a new index named "cntct_contact_id_pk". It's all very deceptive because Oracle does not give you an informative message like:
  "Found existing index XYZ to support constraint..."

Now, if you issue:
  alter table disable constraint cntct_contact_id_pk; ... the "XYZ" index gets dropped.

In this case, the constraint name and index name are not identical so the RevealNet script will not accurately regenerate this constraint.

One could argue that this situation is rare and it may be. That still doesn't change the fact that the RevealNet script is not 100% accurate. Besides, is it really that rare? What if a developer#1 creates a unique index on a table one month and a month later, developer#2 creates a matching primary constraint? Developer#2 is oblivious to developer#1's index! If developer#2 casually disables and renables his constraint, developer#1's index and his custom settings are lost. If anyone had queries that contained optimization hints like
  "select /*+ index(contact_t XYZ) */...", those queries would be semi-broken!

Because of the way DBA_CONS_COLUMNS and DBA_IND_COLUMNS are laid out, you can't do a simple SQL join to match them up since you have to take into account COLUMN_POSITION. Whether you do self-joins, or embedded PL/SQL as part of SELECT, or use temporary tables to do this, it's still a difficult task.

Oracle themselves reinforce the myth that disabling and renabling constraints is no big deal with statements like this from the "Oracle7 Server Utilies" documentation:

   "To prevent errors like these, it is a good idea to     disable referential integerity constraints when     importing data into existing tables." Oracle doesn't give you a clue as to how difficult it really is to re-enable the constraints accurately.

In article <35DA2A22.71BAE91A_at_deere.com>,   "John P. Higgins" <jh33378_at_deere.com> wrote:
> Please, don't just tell us that 'the $400 RevealNet product is not 100%
> correct'.
> Please, tell us what to watch for.
>
> johnvue_at_gte.net wrote:
>
> > First off, it is my opinion that disabling and re-enabling
> > constraints is one of the most dangerous and error prone
> > activities you can do in Oracle. With the following
> > explanation, you'll see why.
> >
> > You are correct that the ordering of disable/enable
> > constraints matters but there's also another little known
> > caveat to the simplistic:
> > select 'alter table enable constraint ... '
> > from dba_constraints;
> >
> > Let's create a table that exists in tablespace "ts_db1".
> > We want to have the "contact_id" column as the primary key.
> > We know that primary key constraints are enforced with a
> > unique index. Since it's really indexes we're dealing
> > with, we can change their storage parameters like any other
> > standard index. I want this "index" to reside in
> > tablespace "ts_idx1" and NOT in the user's DEFAULT
> > tablespace and NOT in the same tablespace as the table
> > "ts_db1".
> >
> > Here's an example SQL statement for such table:
> >
> > create table contact_t
> > (
> > contact_id number(7),
> > fname varchar(30),
> > constraint cntct_contact_id_pk primary key (contact_id)
> > using index tablespace ts_idx1
> > storage (initial 128K next 128K minextents 1 pctincrease 0)
> > )
> > tablespace ts_db1
> > storage (initial 128K next 128K minextents 1 pctincrease 0);
> >
> > THIS IS THE KEY POINT: The custom storage parameters and
> > custom target tablespace of this constraint generated
> > "index" is STORED WITH THE INDEX (in DBA_INDEXES).
> >
> > THIS IS THE KEY POINT REWORDED: The custom storage
> > settings are NOT STORED WITH THE CONSTRAINT.
> >
> > If we DISABLE or DROP the constraint using:
> >
> > alter table disable constraint cntct_contact_id_pk;
> > ... or ..
> > alter table drop constraint cntct_contact_id_pk;
> >
> > ... the index called "cntct_contact_id_pk" gets dropped.
> >
> > Hence, disabling/dropping the constraint means dropping the
> > index which then means losing the index's custom storage
> > settings.
> >
> > If you ENABLE the constraint again with ...
> >
> > alter table enable constraint cntct_contact_id_pk;
> >
> > ... Oracle will rebuild the behind-the-scenes index but it
> > won't build it the original way we intended. The index now
> > resides in the users's DEFAULT tablespace and has the
> > underlying tablespace's default storage parameters!
> >
> > Sometimes you can get away with the simplistic:
> > select 'alter table enable constraint ... '
> > from dba_constraints;
> > ... type approach because developers don't bother
> > customizing primary key index storage parameters. They'll
> > go through the trouble of customizing free-standing
> > indexes' parameters but not the indexes generated from
> > primary keys.
> >
> > What's the solution then?
> >
> > You can write a more sophisticated & complex script that
> > joins DBA_CONSTRAINTS to DBA_INDEXES to get the storage
> > parameters. This is a little harder than it looks because
> > it's possible for an index to support a constraint but
> > not have the same name as the constraint! To pursue
> > this even further, you'd also have to match up
> > DBA_CONS_COLUMNS to DBA_IND_COLUMNS to find the indexes
> > that don't have identical names to the constraint. In
> > essence, you'd have to query 4 tables to properly
> > generate a bulletproof 'alter table enable constraint ...;'
> > command. A lot of work.
> >
> > There's more to the relationship between constraints and
> > indexes in Oracle than most DBAs are aware of. I don't
> > know of any books that talk about this particular subject
> > to great depth. Unfortunately, there are countless scripts
> > on different websites and books that show the simplistic
> > approach but we know there's a lot more to it than that!
> > Even the seemingly complete constraint script that comes
> > with the $400 RevealNet product is not 100% correct!
> >
> > In article <35D6EE56.96E1197_at_club-internet.fr>,
> > oberco_at_club-internet.fr wrote:
> > > Hello John,
> > >
> > > Why this script won't work for constraint with associated index ??
> > > because of the order of the constraint to disable ??
> > >
> > > thanks for the answer
> > >
> > > johnvue_at_gte.net wrote:
> > >
> > > > Just remember though, if any constraint was made using a STORAGE
> > > > clause to create the associated index in another tablespace or change
> > > > other parameters, this "simple" script won't work.
> > > >
> >
> > -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> > http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Aug 19 1998 - 09:43:03 CDT