Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-00001 on non-unique index!

Re: ORA-00001 on non-unique index!

From: DA Morgan <>
Date: Mon, 06 Nov 2006 10:36:40 -0800
Message-ID: <> wrote:
> I am experiencing a strange (to me anyway) problem with a PL/SQL
> program I am working on.
> Some background first:
> I'm using Oracle
> In the database there is a set of big tables (several million rows in
> some) which are updated daily with new records. When a new record is
> created a date field (datecol) is set to null. These records are
> created by a third party system and this processing cannot be altered.
> My program has to identify the new records that it is interested in,
> mark them by updating the null value in datecol to a specific date
> value, perform some further processing on the marked records and then
> mark as processed by setting the datecol to sysdate.
> The intermediate date value is required because the final step (setting
> the date column to sysdate) is not carried out in the same transaction
> as the marking of the records.
> I pick out the new records I want using the predicate 'datecol is null
> and numcol = 5'. This always performs a full table scan on each of the
> 5 tables I am querying which is slowing my program down, especially for
> the largest tables. The number of rows with a null date is a small
> (typically 5-10 or less) percentage of the total so I decided to try to
> find a way of indexing the records I wish to update.
> Knowing that null keys are not included in an index I decided to create
> a pair of function based indexes (FBI) on each table. The first FBI
> (IX1) uses the expression:
> DECODE(numcol, 5, DECODE(TRUNC(datecol), NULL, '01-JAN-3000', NULL),
> to include only the new records I want in the index.
> The second FBI (IX2) uses the expression:
> DECODE(datecol,'31-DEC-3000','31-DEC-3000',NULL)
> to only index the records I have marked for further processing.
> The problem I am experiencing is as follows.
> The statement:
> UPDATE table
> SET datecol = '31-DEC-3000'
> WHERE numcol = 5
> (
> numcol,
> 5,
> DECODE(TRUNC(datecol), NULL, '01-JAN-3000', NULL),
> ) = '01-JAN-3000';
> fails with the following error:
> ORA-00001: unique constraint (IX2) violated
> IX2 is a non-unique FBI so how Oracle can generate a unique constraint
> violation on it is a mystery to me. Can anyone help?
> Thanks,
> Charles Berger.

Is there be a previously existing unique index on the column?

Daniel A. Morgan
University of Washington
(replace x with u to respond)
Puget Sound Oracle Users Group
Received on Mon Nov 06 2006 - 12:36:40 CST

Original text of this message