Hi All!
I was reviewing some features in Oracle and, basically, every single time I review them I find something new. Seems Oracle Databases’ features are near to infinite and we frequently find some that can really add value to our solutions.
Here is a quick summary I did for a client regarding new Semantic Hints on 11g..
11g New Semantic Hints
In Database version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. They have different working ends but they all tries to avoid ORA-0001 in some specific situations. An important note is that, unlike almost all other hints, this hint has a semantic effect: it changes the actual behavior – not just the optimization – of the SQL. Oracle Docs says about this Hint:
“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.”
Some specifics about each one:
- IGNORE_ROW_ON_DUPKEY_INDEX: “When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row.”
- CHANGE_DUPKEY_ERROR_INDEX: “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.”
- RETRY_ON_ROW_CHANGE: “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.”
The use of those hints, however, is only recommended for pretty specific ends and should be avoided if possible. Not only for having some related bugs, but to add some complexity to known operations like bulk inserts and similar.
Other important hint is the Result Cache Hint, mentioned some months ago, under Result Cache Post.
Cheers!
