HOW TO
Improve performance when working with multi-version views in Oracle
Summary
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.
Procedure
To set the current version prior to querying the multi-version view, execute the following SDE stored procedure:Code:
SQL> EXECUTE sde.version_util.set_current_version('SDE.DEFAULT');
PL/SQL procedure successfully completed.
Query the multi-version view. The user or application developer needs to determine the frequency in which they need to set the current version. Because the state_id that the version references is cached, if the version and versioned table the multi-version view references is updated, the changes will not be included in the result set until the current state has been set (by once again executing sde.version_util.set_current_version).
Article ID: 000010652
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App