ArcGIS users working with versioned data and Oracle 10.2.0.4 encountering Oracle bug 7155655

Description

ArcGIS users working with versioned data and Oracle 10.2.0.4 have the potential of encountering Oracle bug 7155655.

The bug is specific to ESRI versioning queries when the Oracle optimizer chooses an execution plan using the view pushed predicate operation. When this occurs, there is a potential that the query will not return the correct result set and can lead to errors in ArcGIS while editing, or data inconsistencies in a versioned object class (occurring when compressing the geodatabase or saving an edit session).

Cause

The cause is specific to Oracle 10.2.0.4 and only happens when the Oracle optimizer selects an execution plans containing the operation 'VIEW PUSHED PREDICATE'. For specific details on the Oracle bug, please see Oracle Metalink and search for bug 7155655.

Not all versioning queries that use this operation and execution plan are affected by the problem, and all the possible SQL statements and conditions that might encounter this Oracle bug (one specific case was identified by a user attempting to delete an object with ArcGIS) are unknown to ESRI.

The following is an example of an execution plan for a versioning query with the operation 'VIEW PUSHED PREDICATE' that encountered the Oracle bug.

Code:
----------------------------------------
| Id  | Operation                      |
----------------------------------------
|*  1 |  FILTER                        |
|   2 |   NESTED LOOPS OUTER           |
|   3 |    NESTED LOOPS                |
|*  4 |     TABLE ACCESS BY INDEX ROWID|
|*  5 |      INDEX RANGE SCAN          |
|*  6 |     INDEX UNIQUE SCAN          |
|   7 |    VIEW PUSHED PREDICATE       |
|*  8 |     FILTER                     |
|   9 |      NESTED LOOPS              |
|* 10 |       INDEX RANGE SCAN         |
|* 11 |       INDEX UNIQUE SCAN        |
----------------------------------------

Workaround