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 -> ORA-00001 on non-unique index!

ORA-00001 on non-unique index!

From: <charles.a.berger_at_gmail.com>
Date: 6 Nov 2006 04:40:34 -0800
Message-ID: <1162816834.388308.71950@m7g2000cwm.googlegroups.com>


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

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), 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
AND DECODE
(

  numcol,
  5,
  DECODE(TRUNC(datecol), NULL, '01-JAN-3000', NULL),   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. Received on Mon Nov 06 2006 - 06:40:34 CST

Original text of this message

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