Re: [8i] Local unique indexes on a partitionned table - Is it possible ?

Uniqueness doesn't require a unique index to enforce it. In fact, if you declare the *table* columns to be unique, and declare at the time that the constraint should be 'deferrable', you'll get a non-unique index created for you.

Since the optimizer is smart enought to know that the table is unique for the column(s) involved, it treats the index as though it were unique, even though it is not declared to be. Thus there is no performance penalty in using non-unique indexes to enforce uniqueness.

Plus, in 8.1.6 documentation a while back, I saw a recommendation from Oracle *not* to use the "UNIQUE" keyword whilst declaring indexes.

So: dispense with the unique index, as it's unnecessary. Constrain the table instead. And the partitioning problem goes away!

Regards
HJR "Frederic Payant" <fpayant_at_club-internet.fr> wrote in message news:hm0aguo82gnqrs9vvurgt79t6gnl8q6tl8_at_4ax.com...
> Hi,
>
> I'm working on partitioned tables which are purged by droping and
> recreating partitions.
> Because we are in an OLTP environment I don't want to have to rebuild
> periodically my indexes.
> But, it seems impossible to create a UNIQUE and LOCAL index unless it
> contains the key of partitioning.
> Part of my indexes are unique and don't contain this field.
> Is there a way to have them local and unique anyway or may I have to
> abandon the possibility of testing uniqueness with the index ?
>
> If unique local index (or any work around) is impossible, what do you
> think about a trigger for checking uniqueness ? Will it be costy ? I'm
> in an OLTP environment where performance is an issue and with
> thousands of transactions by second.
>
>
> Environment is Oracle 817 (probably OEM), with Solaris 8
>
> Any advice will be helpfull (and welcome)
>
>
> Regards
> Frederic PAYANT - junior DBA ;-)
Received on Tue Jun 11 2002 - 03:52:45 CDT