Re: (Non)Unique Index Vs Unique Constraint

(I'm resending my yesterday's post because it seems to have got lost)

Dennis,

You can have a unique constraint with a non-unique index. This is documented and expected behaviour.

Actually, it's fairly easy for Oracle to enforce unique constraint using a a non-unique index. It just traverses to the key's first occurrence in index leaf block and then checks for the immediate next key in the same leaf block. If it is a different value, then we know our key is unique, because keys in index leaf blocks are always ordered and any equal values would be next to each other. But if the next key IS exactly the same as the first one, then we know that unique constraint is violated and an error will be returned.

It gets a bit more complicated when our key doesn't have any more keys immediately next to it in a leaf block. In that case Oracle follows the "next leaf" pointer and reads in the next block in chain and checks for the key value there. And if we had lot's of completely empty leaf blocks next in chain, then Oracle has to read in every single one of them until it either finds a key value there or hits the last block, where the "next pointer" is zero.

(If you wanted to say here that "hey, a b*-tree index can't have a completely empty block in it, because when the last row is deleted from a block, it is freed", then there comes an internal optimization into play. When the last row from an index block is deleted, the block is put in index segment freelist, but it isn't actually removed from b*-tree chain. It will be removed when the leaf block is reused by someone else, thus causing additional IO because leaf block prior and next to it have to be updated. This mechanism probably helps to deal better with rollbacks of large deletes or updates (updates actually mean deletes+inserts for an index key anyway). This behaviour can easily be revealed using a large delete, small insert and a treedump).

I think that it is reasonable to make all indexes in your system non-unique and use unique constraints on them where needed. The reasoning behind it is that if you have some maintenence or conversion operations to do on a large table, which could have use for temporary non-uniqueness, then it's much cheaper to disable/reenable the constraint than drop and build the index.

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, January 17, 2004 11:09 PM

> Jay
> That is a good one. The question is: "How is the uniqueness constraint
> being enforced when the index is nonunique?" Offhand I would have assumed
> your constraint would have been rejected since the index is nonunique --
> nope. Then I would have guessed the index would have been converted to a
> unique index -- it isn't. Then I was skeptical whether the constraint was
> really being enforced, but it is. The next question is how the uniqueness
is
> being enforced if the index is not unique.
> Now, if you add some duplicate values to the table, then try to add the
> constraint, you receive:
>
> alter table index_test add constraint index_test_uk1 UNIQUE(c1)
> *
> ERROR at line 1:
> ORA-02299: cannot validate (SILT.INDEX_TEST_UK1) - duplicate keys found
>
> My guess, and this is only a guess, is that the nonunique index with a
> unique constraint executes the code for a unique index. I ran an insert on
> each version and the plan looks the same.
> Of course, if you go around doing funny stuff like this, the person
that
> eventually takes over your job duties will probably really bless your name
> each time they stumble onto something like a unique constraint on a
> nonumique index.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
> -----Original Message-----
> Sent: Friday, January 16, 2004 9:14 PM
> To: Multiple recipients of list ORACLE-L
>
>
> All,
>
> Please enlighten this Junior DBA.
>
> Which method is more efficient? When should I go for option (1)?
>
> 1)NON-UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
>
> 2)UNIQUE index Vs Unique Constraint
> drop table index_test;
> create table index_test(c1 number,c2 varchar2(20));
> create UNIQUE index i1 on index_test(c1);
> alter table index_test add constraint index_test_uk1 UNIQUE(c1);
>
> Thanks in advance,
> Jay
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jay
> INET: jaysingh1_at_optonline.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).