Re: Disabling Existing Constraints and Index dropped

Fascinating example, Richard, but not the issue I was addressing. Which was the statement, unadorned, that "Well then u loose uniqueness in your index and you incur read costs for unique searches. Not much but why should you ?".

The "not much" comment implied identical indexes, with just a difference between unique and non-unique.

And that's just plain not right, as you & I agree.

Throw in 58 extra columns, and I suspect the degradation in performance could be quite significant. But I don't think that's what 'Ender Wiggin' was on about with his comment.

Regards
HJR "Richard Foote" <Richard.Foote_at_oracle.com> wrote in message news:3D4FCB5D.A9171B85_at_oracle.com...
> 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>
> > >
Received on Tue Aug 06 2002 - 14:39:56 CDT