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

Unfortunately you can't use a trigger to enforce uniqueness in these circumstances. Someone has already mentioned the performance problem of the need to scan every partition. More important, however, is the fact that READ-CONSISTENCY will ensure that the trigger will not see a conflicting row from another session if it has been inserted but not yet committed.

Your only option is to have a global (or globally partitioned) index - and then you are in trouble when you try to add/drop/truncate partitions.

I suppose you could work out a system of keeping a list of the unique keys (which might therefore be much smaller than table) in a non-partitioned IOT. Then have a before-insert trigger which inserts a row into this IOT. The effects, and limitations, would be similar to having a global index, but it might give you some options to work around your most important problems.

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

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

Frederic PAYANT wrote in message ...


>Thanks a lot Howard and Jonathan for your answers.

>

>If nor a unique constraint, nor a unique index are possible locally,

>is there any issue for ensuring the unicity of my index.

>

>Does the idea of testing uniqueness into a trigger on insert seems you

>clever ? (remember that I have a big constraint on performance, and

>that there will be, anyway, an index (not unique) on that key. Another

>important point is that I'll have 90 partitions on that table)

>

>Are there other ways to ensure the uniqueness of the key ?

>

>Many thanks by advance

>

>

>Best regards

>Frederic PAYANT - junior DBA ;-)