dyou98_at_aol.com (D.Y.) wrote in message news:<f369a0eb.0206110920.7094f329_at_posting.google.com>...
> 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.
> >
>
> There are workarounds. But if your partition key is not part of the unique
> constraint, Oracle will have to probe every index partition to validate the
> uniqueness. Depending on the number of partitions there could be significant
> performance on insert and update. Would global index work for you?
^^^^^^^^^^^
should have said performance penalty.
>
> >
> > 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 - 21:22:07 CDT
