| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Disabling Existing Constraints and Index dropped
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
>> > 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
>> > > > of points though. >> > > > >> > > > Firstly, it's not good practice to just create or enable a Primary
>> > > > or Unique key and let Oracle just create the index on its own. I
>> > > > strongly recommend the use of the USING INDEX clause to control how
>> > > > index is created (such as it's name and tablespace). Note 9i allows
>> > > > to define the entire create index syntax when creating/enabling
>> > > > constraints. >> > > > >> > > > Also note Oracle doesn't necessarily create or drop the indexes >> > > > associated with the constraint. If the index being used by Oracle >> > > > is
>> > > > unique (possible for deferrable constraints) then the index is not >> > > > dropped automatically (although 9i can override this default
>> > > > If an existing index could be used to police a new constraint (in
>> > > > the leading columns of an existing index match the column
>> > > > 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
>> > > unique searches. Not much but why should you ? >> > > >> > > > >> > > > Cheers >> > > > >> > > > Richard >> > > > >> > > > >> > > > phil wrote: >> > > >> >> > > >> Hi, >> > > >> >> > > >> If I disable an existing constraints (primary key), it seems the
>> > > >> would be dropped too. >> > > >> How about I enable the primary key constraints again, will it
>> > 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
>> > > >> >> > > >> 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. Received on Tue Aug 06 2002 - 22:11:59 CDT
 |
 |