Re: ORA-00001 on non-unique index!

charles.a.berger_at_gmail.com 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 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.

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

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org