Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Disabling Existing Constraints and Index dropped

Re: Disabling Existing Constraints and Index dropped

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 6 Aug 2002 19:20:31 +1000
Message-ID: <aio4au$1s0$1@lust.ihug.co.nz>


There is ZERO performance penalty from reading a non-unique index which is being used to enforce a primary or unique key constraint (because said constraint is created 'deferrable').

Zero.

The optimizer is smart enough to realise that although the index may be declared to be non-unique, since it is being used to enforce a unique/primary key constraint, the *contents* of the index are actually unique -and it treats it as such.

Regards
HJR "Ender Wiggin" <ender_wiggin29plus1_at_yahoo.com> wrote in message news:NCH39.381$Q9.183022_at_newsfeed.slurp.net...
> Read below for my single comment
>
> Richard Foote wrote:
>
> > Hi Phil,
> >
> > Yes, Oracle will create the index automatically again for you. A couple
> > of points though.
> >
> > Firstly, it's not good practice to just create or enable a Primary Key
> > or Unique key and let Oracle just create the index on its own. I would
> > strongly recommend the use of the USING INDEX clause to control how the
> > index is created (such as it's name and tablespace). Note 9i allows you
> > to define the entire create index syntax when creating/enabling these
> > constraints.
> >
> > Also note Oracle doesn't necessarily create or drop the indexes
> > associated with the constraint. If the index being used by Oracle is non
> > unique (possible for deferrable constraints) then the index is not
> > dropped automatically (although 9i can override this default behavior).
> > If an existing index could be used to police a new constraint (in that
> > the leading columns of an existing index match the column constraints),
> > then Oracle will use the existing index and will not create an
> > unnecessary index.
> >
> > So there's a fair bit to all this :)
>
> Well then u loose uniqueness in your index and you incur read costs for
> unique searches. Not much but why should you ?
>
> >
> > Cheers
> >
> > Richard
> >
> >
> > phil wrote:
> >>
> >> Hi,
> >>
> >> If I disable an existing constraints (primary key), it seems the index
> >> would be dropped too.
> >> How about I enable the primary key constraints again, will it create
the
> >> index or I have to create the index by myself?
> >>
> >> Thanks for your help.
> >>
> >> Phil
> >>
> >> Examples:
> >> SQL> DROP INDEX SCOTT.PK_EMP;
> >> DROP INDEX SCOTT.PK_EMP
> >> *
> >> ERROR at line 1:
> >> ORA-02429: cannot drop index used for enforcement of unique/primary key
> >>
> >> SQL> ALTER TABLE SCOTT.EMP DISABLE PRIMARY KEY;
> >>
> >> Table altered.
> >>
> >> SQL> DROP INDEX SCOTT.PK_EMP;
> >> DROP INDEX SCOTT.PK_EMP
> >> *
> >> ERROR at line 1:
> >> ORA-01418: specified index does not exist
> >>
> >> SQL> ALTER TABLE SCOTT.EMP ENABLE PRIMARY KEY;
> >>
> >> Table altered.
> >>
> >> SQL>
>
Received on Tue Aug 06 2002 - 04:20:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US