How To: Report the length of a version lineage in Oracle
Instructions provided describe how to report the length of a version lineage in Oracle, and provides a SQL statement for returning information on the length of each version lineage.
Each version references a specific state in a versioned geodatabase. A collection of states define a lineage. States are created in a hierarchical manner, where each state, excluding state 0, has a parent state.
As versions are edited, new states are created. When the version is saved, the version is updated to reference the last state in the edit session. Over time, as the version continues to be edited and saved, the length of the version's lineage grows.
When ArcGIS queries a versioned table for any given version, the version's lineage is used to derive the table's versioned representation (by joining the versioned table's adds, deletes and sde.state_lineages table). Performance can be impacted by the length of the version's lineage, because of the number of states that must be joined between the sde.state_lineages table and the delta tables. Based upon the number of states in the version's lineage, the Oracle optimizer may choose a hash join verses a nested loops join, when performing the join operation between the sde.state_lineages table and the delta tables.
To ensure optimal performance when working with versioned data, it is recommended to compress as frequently as possible. For example, compress every night. By compressing the versioned geodatabase it ensures that the length of every version's lineage is as short as possible.
To generate a list of each version's lineage length, connect as the SDE user in SQL*Plus and execute the following SQL statement.
SQL> SELECT v.owner||'.'||v.name "VERSION NAME", COUNT(sl.lineage_id) "LINEAGE LENGTH"
2 FROM sde.states s, sde.state_lineages sl, sde.versions v
3 WHERE s.lineage_name = sl.lineage_name
4 AND sl.lineage_id <= s.state_id
5 AND v.state_id = s.state_id
6 GROUP BY v.owner, v.name, sl.lineage_name
7 ORDER BY "LINEAGE LENGTH";
VERSION NAME LINEAGE LENGTH
- Discover the number of rows in the adds table for the DEFAULT version's lineage for a specific object class