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

'Fraid not.
The manuals may be a little ambiguous here.

It is not the uniqueness of the index that matters when looking at locally partitioned indexes, it is the uniqueness of the constraint you are trying to enforce.

If columns (a,b,c) are constrained to be unique, then Oracle demands that no two rows with the same values for (a,b,c) should be able to exist in different partitions of the index, otherwise Oracle would have to check every partition of the index every time you inserted a row. This would mean that every partition had to be on-line and available all the time.

Indexes representing unique constraints may only be locally partitioned if they contain the partitioning columns of the table.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Howard J. Rogers wrote in message ...


>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 ;-)

>

>