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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> :

>
>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.

Oooops, that's true, I can't do it like that ...

>...

>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.
Advice looks good. I'll propose it to my project manager

Thanks a lot for your answers

Regards
Frederic PAYANT - junior DBA ;-) Received on Thu Jun 13 2002 - 14:55:19 CDT