Enhance performance when working with a multi-version view in Oracle by setting the current version with the Oracle procedure to sde.version_util.set_current_version. Setting the current version enables the Oracle session to cache the state of the database that the version references.
Each time a multi-version view is queried, the view must obtain the state of the database used to derive the result set. When a version has not been set by way of the stored procedure, the default behavior of the view is to obtain the state of the system for which the DEFAULT version references. To obtain the value for the state, the multi-version view internally executes a recursive query against the sde.versions table each time the multi-version view is referenced.
It is the cost of this additional recursive query that can be avoided when a version has been first set by way of the stored procedure sde.version_util.set_current_version.
SQL> EXECUTE sde.version_util.set_current_version('SDE.DEFAULT');
PL/SQL procedure successfully completed.