Thursday, June 13, 2013

IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement 11g new sql feature

With INSERT INTO TARGET...SELECT...FROM SOURCE, a unique key for some to-be-inserted rows may collide with existing rows. The IGNORE_ROW_ON_DUPKEY_INDEX allows the collisions to be silently ignored and the non-colliding rows to be inserted. A PL/SQL program could achieve the same effect by first selecting the source rows and by then inserting them one-by-one into the target in a block that has a null handler for the DUP_VAL_ON_INDEX exception. However, the PL/SQL approach would take effort to program and is much slower than the single SQL statement that this hint allows. This hint improves performance and ease-of-programming when implementing an online application upgrade script using edition-based redefinition.

No comments:

Post a Comment