Re: ORA-00001 on non-unique index!

DA Morgan wrote:
> charles.a.berger_at_gmail.com wrote:
>
> > Otherwise I'm thinking it must be a bug in Oracle, although a search of
> > Metalink yesterday revealed no matches.
> >
> > Cheers,
> >
> > Charles.
>
> Lets find out. Post the DDL for the table and all of its indexes and
> constraints so we can try it out in various versions. Be sure to
> indicate the version where you are finding the issue.

Here's the DDL:

CREATE TABLE sdiprdmst
(

  ORG_LVL_CHILD           NUMBER(12),
  PRD_LVL_CHILD           NUMBER(12),
  ISS_TECH_KEY            NUMBER(12),
  ALT_TECH_KEY            NUMBER(12),
  TRAN_TYPE               CHAR(1),
  AUDIT_NUMBER            NUMBER(12),
  ISS_TYPE                CHAR(1),
  ORG_LVL_NUMBER          NUMBER(12),
  PRD_LVL_NUMBER          CHAR(15),
  PRD_LVL_PARENT          NUMBER(12),
  PRD_LVL_ID              NUMBER(5),
  PRD_LVL_PARENT_NUM      CHAR(15),
  PRD_NAME_FULL           VARCHAR2(50),
  PRD_TARGETGM            NUMBER(7,3),
  PRD_LVL_ACTIVE          CHAR(1),
  PRD_STYLE_IND           CHAR(1),
  PRD_STATUS              NUMBER(2),
  GRP_AUDIT_NUMBER        NUMBER(12),
  PRD_PDH_KEY_1           NUMBER(12),
  PRD_PDH_NAME_1          CHAR(30),
  PRD_PDD_KEY_1           NUMBER(12),
  PRD_PDD_CODE_1          CHAR(6),
  PRD_PDH_KEY_2           NUMBER(12),
  PRD_PDH_NAME_2          CHAR(30),
  PRD_PDD_KEY_2           NUMBER(12),
  PRD_PDD_CODE_2          CHAR(6),
  PRD_PDH_KEY_3           NUMBER(12),
  PRD_PDH_NAME_3          CHAR(30),
  PRD_PDD_KEY_3           NUMBER(12),
  PRD_PDD_CODE_3          CHAR(6),
  DATE_CREATED            DATE,
  DOWNLOAD_DATE_1         DATE,
  DOWNLOAD_DATE_2         DATE,
  PRD_UOM_SIZE            NUMBER(13,7),
  PRD_SLL_UOM             CHAR(6),
  PRD_SLL_NAME            VARCHAR2(20),
  PRD_COMP_UOM            CHAR(6),
  PRD_COMP_NAME           VARCHAR2(20),
  PRD_CONV_QTY            NUMBER(13,7),
  PRD_CROSS_DOCK          CHAR(1),
  PRD_LVL_NUMBER_OLD      CHAR(15),

CREATE INDEX XXX_SDIPRDMST_PLC ON sdiprdmst (PRD_LVL_CHILD); CREATE INDEX XXX_SDIPRDMST_IX1 ON sdiprdmst (DECODE(TO_CHAR(TRUNC("DOWNLOAD_DATE_2")),NULL,'01-JAN-3000',NULL)); N.B. This index was my first attempt to create a function based index on the null download_date_2 columns but, as it contained records for all the values of ISS_TECH_KEY, it wasn't selective enough and will be dropped. It was replaced with XX_SDIPRDMST_IX3.

CREATE INDEX XXX_SDIPRDMST_IX2 ON sdiprdmst (DECODE("DOWNLOAD_DATE_2",'31-DEC-3000','31-DEC-3000',NULL)); CREATE INDEX XXX_SDIPRDMST_IX3 ON sdiprdmst (DECODE("ISS_TECH_KEY",5,DECODE(TO_CHAR(TRUNC("DOWNLOAD_DATE_2")),NULL,'01-JAN-3000',NULL),NULL)); CREATE INDEX SDIPRDMST_UK1 ON sdiprdmst
(ORG_LVL_NUMBER); CREATE UNIQUE INDEX SDIPRDMSTP1 ON sdiprdmst (ORG_LVL_CHILD, ALT_TECH_KEY, PRD_LVL_CHILD, ISS_TECH_KEY, TRAN_TYPE, AUDIT_NUMBER); ALTER TABLE sdiprdmst ADD (
  CONSTRAINT SDIPRDMSTP1
 PRIMARY KEY
 (ORG_LVL_CHILD, ALT_TECH_KEY, PRD_LVL_CHILD, ISS_TECH_KEY, TRAN_TYPE, AUDIT_NUMBER);
>
> A few rows of sample data the produce any problem you think data related
> too if that is the case (can't remember).

Not sure what exactly you want. If it's any help I am expecting my query to update 42 out of 1884666 rows in the table. So the index XXX_SDIPRDMST_IX2 should then contain pointers to 42 records.

> --
> 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
Received on Wed Nov 08 2006 - 11:09:59 CST