Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: [8i] Local unique indexes on a partitionned table - Is it possible ?

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

From: D.Y. <dyou98_at_aol.com>
Date: 11 Jun 2002 10:20:10 -0700
Message-ID: <f369a0eb.0206110920.7094f329@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?

>
> 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 - 12:20:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US