English

How To: Report the length of a version lineage in Oracle

Summary

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.

Procedure

To generate a list of each version's lineage length, connect as the SDE user in SQL*Plus and execute the following SQL statement.


  1. Code:
    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
    ------------------------- --------------
    TOM.WORKORDER_I0C6531 21
    TOM.EDITOR_TB 25
    ERIK.WORKORDER_I910313 78
    BRENT.DESIGN_F831 92
    BRENT.DESIGN_F831_ALT_1 94
    BRENT.DESIGN_F831_ALT_2 95
    SDE.MAPPING_DEFAULT 138
    SDE.LANDBASE 169
    SDE.DEFAULT 249
    ERIK.EDITOR_EH 275
    STEVE.EDITOR_SM 289

Related Information