Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle 11g Hint -IGNORE_ROW_ON_DUPKEY_INDEX

Data has to be loaded to tables that have a unique index or constraint
as shown in the example below.  The table dbt_nf11g_demo has a unique 
index on the columns dbt_idx and dbt_date_id.

CREATE TABLE dbt_nf11g_demo
(dbt_idx          NUMBER NOT NULL,
 dbt_date_id      NUMBER(8) NOT NULL,
 created_date     DATE  DEFAULT SYSDATE NOT NULL,
 updated_date     DATE
);

CREATE UNIQUE INDEX dbt_nf11g_uidx ON
dbt_nf11g_demo
(dbt_idx,
 dbt_date_id
);

Without Oracle 11g Hint -IGNORE_ROW_ON_DUPKEY_INDEX

Unique constraint violation (ORA-00001) error is raised by the SQL statement shown
below, since the query creates duplicate rows.

INSERT INTO dbt_nf11g_demo
 (dbt_idx, dbt_date_id)
SELECT  
 MOD(level,2)+1 dbt_idx,
 TO_CHAR(sysdate+MOD(level,2),'yyyymmdd')
FROM DUAL
CONNECT BY LEVEL <= 6;

Without Oracle 11g Hint - ORA-00001 Error

Using the Oracle 11g hint IGNORE_ROW_ON_DUPKEY_INDEX in the insert statement,
two rows are loaded successfully and four duplicate rows are ignored as 
shown in below.

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(dbt_nf11g_demo(dbt_idx,dbt_date_id)) */
 INTO dbt_nf11g_demo 
 (dbt_idx, dbt_date_id)
SELECT 
 MOD(level,2)+1 dbt_idx,
 TO_CHAR(sysdate+MOD(level,2),'yyyymmdd')
FROm DUAL
CONNECT BY LEVEL <= 6;

With Oracle 11g Hint -IGNORE_ROW_ON_DUPKEY_INDEX


Without this hint, analytic function row_number can be another option.



Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 20, 2014

  39885