Re: Disabling Existing Constraints and Index dropped

Ender Wiggin wrote:
>
> Howard J. Rogers wrote:
>
> > 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>
> >> > >
>
> Sorry for the confusion; what I meant is this
>
> create table test ( idx number(10,0) ) ;
> create index x_idx on test(idx) ; -- not unique
> alter table add constraint test_pk unique(idx) ;
>
> after this if you use such a select statement, that's what explain plan
> produces
>
> SQL> select * from test where idx = 1
> 2 /
>
> IDX
> ----------
> 1
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> 1 0 INDEX (RANGE SCAN) OF 'X_IDX' (NON-UNIQUE)
>
> Even though the column is unique and I know it, there is a range scan on the
> index, which means oracle can read one extra block.

Hi Ender,

But it doesn't (mean that Oracle can read one extra block).

I think the RANGE SCAN in the execution plan is confusing you. As Oracle searches for a particular value in the index, because it's a non-unique index, the execution plan isn't aware of the purpose for the access and hence suggests that a range scan might be necessary.

However in actual fact, just think logically why Oracle is searching the index. It's trying to determine if a particular value is there or not. As it traverses the index and finds the leaf page in question, Oracle performs a simple check. If the value is there, end of check, the constraint is violated. *There is no need to check for any other values*, the constraint has already been violated (and never mind the fact there can only be one committed value). Obviously, if the value is not there, that's Hunky Dory, the constraint is not violated (but again, no need to check any further).

So it really doesn't matter per se if the index used to police the constraint is unique or non unique, Oracle performs exactly the same amount of work in either case.

Don't worry too much about my ramblings with regard to the use of a previously created concatinated index (although you may want to take note), just clouding the waters in my unique style.

As Howard said, if the indexes are the same, the fact that they are unique or non-unique makes no difference.

Cheers

Richard