Three new "hints"

In version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. These are not normal hints like we know them. Oracle writes:

Note:
The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

That's strange. Why does Oracle mix up the concept of a hint by introducing three new hints that have a semantic effect? Now you have two kind of hints. You have optimizer hints and you have semantic hints. Both are silently ignored when you make a syntax error. But there is no way to tell with which kind you are dealing, unless you know them by heart. And it becomes harder to explain. It means you cannot advice to just gather statistics and remove all hints anymore. Couldn't this be solved by adding an extra keyword? Or were they reluctant to introduce another deviation from the ANSI standard? On the other hand, we now have some extra options available, and that's good. Let's explore them.


IGNORE_ROW_ON_DUPKEY_INDEX

From the documentation: "When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row."

An example:

rwijk@ORA11GR2> create table mytable
  2  ( id   number(2)   primary key
  3  , name varchar2(3) not null
  4  )
  5  /

Table created.

rwijk@ORA11GR2> insert into mytable values (4,'old')
  2  /

1 row created.

rwijk@ORA11GR2> insert into mytable values (9,'old')
  2  /

1 row created.

rwijk@ORA11GR2> select * from mytable
  2  /

        ID NAM
---------- ---
         4 old
         9 old

2 rows selected.

rwijk@ORA11GR2> insert /*+ ignore_row_on_dupkey_index(mytable(id)) */ into mytable
  2  ( id
  3  , name
  4  )
  5   select level
  6        , 'new'
  7     from dual
  8  connect by level <= 10
  9  /

8 rows created.

rwijk@ORA11GR2> select *
  2    from mytable
  3   order by id
  4  /

        ID NAM
---------- ---
         1 new
         2 new
         3 new
         4 old
         5 new
         6 new
         7 new
         8 new
         9 old
        10 new

10 rows selected.

So the insert statement with the ignore_row_on_dupkey_index was specified to insert 10 rows, but due to the current contents of the table, only 8 rows were actually inserted, since 4 and 9 already exist. In 10g Release 2 you could achieve a similar effect by using the DBMS_ERRLOG package. Using the DBMS_ERRLOG package is a bit more cumbersome and it is different since it logs ALL errors, not just the unique key violations.


CHANGE_DUPKEY_ERROR_INDEX

From the documentation: "The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.". An example with a table containing a primary key constraint and a unique key constraint:

rwijk@ORA11GR2> create table t
  2  ( id   number(2)
  3  , name varchar2(3) not null
  4  , constraint t_pk primary key (id)
  5  , constraint t_uk unique(name)
  6  )
  7  /

Table created.

rwijk@ORA11GR2> insert into t values (1,'bla')
  2  /

1 row created.

Whether the primary key constraint or the unique key constraint is violated, you'll always get an ORA-00001 error message:

rwijk@ORA11GR2> insert into t values (1,'abc')
  2  /
insert into t values (1,'abc')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_PK) violated


rwijk@ORA11GR2> insert into t values (2,'bla')
  2  /
insert into t values (2,'bla')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_UK) violated

With the change_dupkey_error_index hint you can reroute one of the ORA-00001 error messages to an ORA-38911 error. Which unique key violation is rerouted, depends on the columns or unique index name specified after the table name in the hint.

rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(name)) */ into t
  2  values (2,'bla')
  3  /
insert /*+ change_dupkey_error_index (t(name)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_UK) violated


rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(id)) */ into t
  2  values (1, 'abc')
  3  /
insert /*+ change_dupkey_error_index (t(id)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_PK) violated

This can be handy in PL/SQL to distinguish one unique key violation from the other, without having to parse the error message for the constraint name:

rwijk@ORA11GR2> declare
  2    e_name_uk_violated exception;
  3    pragma exception_init(e_name_uk_violated,-38911);
  4  begin
  5    insert /*+ change_dupkey_error_index (t,t_uk) */ into t values (1,'abc');
  6  exception
  7  when e_name_uk_violated then
  8    dbms_output.put_line('Name should be unique, and this name already exists.');
  9  when dup_val_on_index then
 10    dbms_output.put_line('Some other unique constraint was violated');
 11  end;
 12  /
Some other unique constraint was violated

PL/SQL procedure successfully completed.

rwijk@ORA11GR2> declare
  2    e_id_pk_violated exception;
  3    pragma exception_init(e_id_pk_violated,-38911);
  4  begin
  5    insert /*+ change_dupkey_error_index (t,t_pk) */ into t values (1,'abc');
  6  exception
  7  when e_id_pk_violated then
  8    dbms_output.put_line('Id should be unique, and this Id already exists.');
  9  when dup_val_on_index then
 10    dbms_output.put_line('Some other unique constraint was violated');
 11  end;
 12  /
Id should be unique, and this Id already exists.

PL/SQL procedure successfully completed.

RETRY_ON_ROW_CHANGE

This one puzzles me. The documentation says: "When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.".

Ok, I can reproduce that. First create a table t with 50,000 rows and add a row level trigger that logs autonomously into a table called logtable. This way we can monitor the progress of the update statement in a separate session. And it slows down the update considerably, which is a good thing for the test.

rwijk@ORA11GR2> create table t
  2  ( id  number(9) primary key
  3  , col number(9) not null
  4  )
  5  /

Table created.

rwijk@ORA11GR2> insert /*+ append */ into t
  2   select level
  3        , level
  4     from dual
  5  connect by level <= 50000
  6  /

50000 rows created.

rwijk@ORA11GR2> create table logtable
  2  ( last_id                 number(9)
  3  , number_of_processed_ids number(9)
  4  )
  5  /

Table created.

rwijk@ORA11GR2> insert into logtable values (null,0)
  2  /

1 row created.

rwijk@ORA11GR2> create procedure p (p_id in t.id%type)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    update logtable
  6       set last_id = p_id
  7         , number_of_processed_ids = number_of_processed_ids + 1
  8    ;
  9    commit;
 10  end;
 11  /

Procedure created.

rwijk@ORA11GR2> create trigger t_aru
  2    after update on t
  3    for each row
  4  begin
  5    p(:new.id);
  6  end;
  7  /

Trigger created.

And now we do an update with the retry_on_row_change hint:

rwijk@ORA11GR2> update /*+ retry_on_row_change */ t
  2     set col = col * -1
  3  /

And while this statement is running, open a second session and run this:

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     14754                   14754

1 row selected.

rwijk@ORA11GR2> update t
  2     set col = 888888
  3   where id = 40000
  4  /

1 row updated.

rwijk@ORA11GR2> commit
  2  /

Commit complete.

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     17507                   17508

1 row selected.

So the row with id 40000 is updated and committed, while our statement from session 1 has not reached this row yet.

After session 1 completed the update it says:

50000 rows updated.

But here is what happened (from session 1):

rwijk@ORA11GR2> select *
  2    from t
  3   where id between 39999 and 40001
  4  /

        ID        COL
---------- ----------
     39999     -39999
     40000    -888888
     40001     -40001

3 rows selected.

Session 2 has set COL to 888888, and session 1 has set this value to -888888.

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     50000                   89922

1 row selected.

By the number_of_processed_ids column you can deduce that the statement was restarted once it has fetched the block with ID 40000 in it. I also checked it in session 2 by reexecuting the last statement several times. The last_id column dropped back to 1 and the entire statement was re-executed. This leads to a total of 89,922 (50,000 + almost 40,000) number of processed ID's. So the statement was retried on detecting a row change. And the table doesn't need the ROWDEPENDENCIES clause to make this happen.

But what's the point? Oracle has its write consistency mechanism that makes sure a statement gets restarted under the covers if during a DML a row has been changed that has not been locked yet. For example, if we add a where clause to the update statement saying "id = col", then the statement sets out to update 50,000 rows. Now, if another session update the col value of ID 40000 to 888888, the update statement of session 1 will detect that automatically and restarts the statement. Without the need for a RETRY_ON_ROW_CHANGE hint:

From session 1 with the same DDL as above:

rwijk@ORA11GR2> update t
  2     set col = col * -1
  3   where id = col
  4  /

And while this statement runs, update the ID 40000 record in session 2:

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     10783                   10783

1 row selected.

rwijk@ORA11GR2> update t
  2     set col = 888888
  3   where id = 40000
  4  /

1 row updated.

rwijk@ORA11GR2> commit
  2  /

Commit complete.

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     12822                   12823

1 row selected.

And then from session 1:

49999 rows updated.

rwijk@ORA11GR2> commit
  2  /

Commit complete.

rwijk@ORA11GR2> select *
  2    from t
  3   where id between 39999 and 40001
  4  /

        ID        COL
---------- ----------
     39999     -39999
     40000     888888
     40001     -40001

3 rows selected.

rwijk@ORA11GR2> select *
  2    from logtable
  3  /

   LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
     50000                   89999

1 row selected.

So here Oracle's write consistency mechanism made the statement rollback and re-execute. Which, by the way, is a good reason to not use autonomous transactions in database triggers: they might execute twice for a single row.

If someone can enlighten me with a use case for the RETRY_ON_ROW_CHANGE hint, I would be happy.