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');
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