0

I have 2 scenarios where I'm using the hint IGNORE_ROW_ON_DUPKEY_INDEX. The first seems to be working fine. The second scenario is failing with the error below and I'm unsure how to fix it.

ORA-38913: Index specified in the index hint is invalid

I'm testing on live SQL so our environments can be the same if you so desire.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

 create table t (
    t_pk integer not null primary key
  );

 insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (t_pk)) */ into t values (1);

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (t_pk)) */ into t values (1);


CREATE table t1(
      seq_num INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       a NUMBER,
       b  DATE,
       c NUMBER,
       d NUMBER,
       e DATE,
       f DATE,
       g DATE,
       h VARCHAR2(1),
constraint t1_pk primary key (a, b,c,d,e, f, g,h)
       );

insert /*+ignore_row_on_dupkey_index (t1 ( t1_pk)) */ INTO t1(
       a
       ,b
       ,c
       ,d
       ,e
       ,f
       ,g
       ,h
 )
VALUES
 (1,
TO_DATE('2021-08-28 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),1,1,
TO_DATE('2021-08-28 13:27:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2021-08-28 13:30:00', 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE('2021-08-28 13:27:20', 'YYYY-MM-DD HH24:MI:SS'), 'G');
Seanny123
  • 8,776
  • 13
  • 68
  • 124
Pugzly
  • 844
  • 3
  • 14

1 Answers1

3

Your syntax for the second case is incorrect. Valid syntax when giving a list of columns is like this:

/*+ignore_row_on_dupkey_index ( table_name ( column_1, column_2, ... )) */

or like this when naming an index:

/*+ignore_row_on_dupkey_index (table_name , index_name) */

In your first example, you named your column t1_pk, so the ( table ( column )) syntax is correct. In your second example, you named your contraint t1_pk, so you need to use the (table,index) syntax form.

See documentation here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Comments.html#GUID-20390275-91A7-49DC-AAD1-A1FE943A4F75

pmdba
  • 6,457
  • 2
  • 6
  • 16