Problem: SDE.STATE_LINEAGES table index growth


The SDE.STATE_LINEAGES table is used to store a database state lineage in a normalized form. This provides a faster method for obtaining a state lineage and the ability to join the table as a predicate clause in a versioned query. Previous releases of ArcSDE stored the state lineages as an additional binary attribute (BLOB) in the SDE.STATES table.

The STATE_LINEAGES table is very dynamic. The growth of the table is based on the frequency of database states being created and deleted. Database states are created for each ArcGIS edit operation and are removed when the edit session is saved.

Therefore, depending on an organization's work flow and the number of editors, the number of rows in the table can grow very quickly. Large organizations can easily expect to have more than one million rows in the table.


The act of inserting and deleting rows from a table directly affects the storage characteristics for any of the table indexes. For example, when a new row is inserted, the value for the indexed column or columns is also inserted into the index. When rows are deleted from the table, the corresponding index key is not removed but is simply flagged as deleted. Therefore, as more rows are inserted into the table, the index storage will continue to increase and will not decrease in size until the index is dropped and re-created.

Solution or Workaround

The frequency of inserts and deletes will determine when the STATE_LINEAGES indexes will need to be dropped and re-created. The index's storage characteristics, the size it currently requires, is not the only reason to rebuild the index. Query performance can ultimately suffer due to the costs of navigating a fragmented index. Therefore, it may become necessary to frequently rebuild the index.

The STATE_LINEAGES table indexes are named LINEAGE_PK (state_id, lineage_id columns) and LINEAGE_ID_IDX (lineage_id column).

Perform the following steps in Oracle.

  1. Drop the existing indexes:

    DROP INDEX lineage_pk;
    DROP INDEX lineage_id_idx.
  2. Create the new indexes. Provide the tablespace in which the index segment will reside and the initial and next extent sizes.

    CREATE UNIQUE INDEX lineage_pk ON state_lineages (state_id, lineage_id) TABLESPACE <tablespace_name> INITRANS 4 PCTFREE 10 PCTUSED 90

    CREATE INDEX lineage_id_idx ON state_lineages (lineage_id) TABLESPACE <tablespace_name> INITRANS 4 PCTFREE 10 PCTUSED 90 STORAGE (INITIAL <size> NEXT <size> MINEXTENTS 1 MAXEXTENTS <value> FREELISTS 4).

  3. Update the STATE_LINEAGES table's database statistics.