Re: Disabling Existing Constraints and Index dropped

Hi Howard,

Whenever I see the word never, or always, or some such phrase, I see it as a challenge :)

I purposely used the phrase "assuming the non unique index Oracle uses has the same height ... ". I'll explain.

Say you have a table with columns a, b, c, ... z

If you add a unique constraint to column c and no indexes currently exist on c, then Oracle creates an index on c. Cool.

If you add a unique index to column c but c is already used in a concatenated index c, d, e, f, g, h, i, j say, then as c is the leading column, Oracle will use this index to police the unique constraint.

However, because of the size of this second index and the small block size etc., the height of this index is greater (say by 1 level) than it's equivalent unique index featuring just column c would have been.

Therefore it is more expensive to traverse this non unique concatenated index, than it's smaller unique brother would have been.

I'm stretching it I know but it's the efficiency of the index that's the issue, not so much whether it's unique or non unique per se. However, if Oracle decides to uses a non unique index because a suitable one already exists, then it *might* be less efficient than a unique index created exclusively to police the constraint.

However, this is a real stretch of an example and all things being equal (and certainly if the same columns are involved) and if the indexes are of the same height, then there is no difference at all in whether an index is unique or not.

Cheers

Richard

"Howard J. Rogers" wrote:
>
> 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>
> >