Discover the number of rows in the adds table for the DEFAULT version's lineage for a specific object class

Summary

A versioned query's performance (response time) can be impacted by the number of rows that must be filtered from the adds and deletes tables.

There is no set number representing the number of rows in the adds or deletes tables that indicate when a versioned query's performance is impacted. Logically, the cost of filtering rows in the delta tables during the execution of the version query impacts performance.

This article provides a reporting mechanism for the administrator to execute a report that shows how many rows are present in the DEFAULT version's lineage for a specific object class.

If the version query's performance is being impacted by the number of rows in the delta tables, the solution is to reconcile all versions with the DEFAULT version, or use the ArcObjects method recommended reconcile order to reconcile just the versions, which are preventing the DEFAULT version from being compressed, and compress the database. By allowing the rows in the delta tables to be compressed back to the object class's base table, less work must be performed by the version query when it is executed, improving performance.

Procedure

Instructions provided demonstrate how to obtain a versioned table's registration_id, define local variables in SQL*Plus, set the variable values from a SQL statement, and execute a statement to return the number of rows in the delta tables for the DEFAULT version's lineage, the percentage of rows in the adds and deletes tables for the DEFAULT version's lineage, and the total number of rows in the adds and deletes table.

In the following example, the objective is to understand how many rows are present in the DEFAULT version's lineage for the feature class, Primary Overhead, delta tables.

1. Identify the registration_id for the versioned table that is being used in the report. Using SQL*Plus, it is possible to execute a query against the ArcSDE table_registry to obtain a table's registration_id value.

SQL> SELECT registration_id
  2  FROM sde.table_registry
  3  WHERE owner = 'ADMIN' AND table_name = 'PRIMARYOH';

REGISTRATION_ID
---------------
             67

2. Define and set the local variables in SQL*Plus, which is used as bind variables in the SQL statement to generate the report.
   • Declare the variables and their type definitions:

SQL> VARIABLE lineage_id NUMBER;
SQL> VARIABLE lineage_name NUMBER;

• • Set each variable's values:

SQL> BEGIN
  2    SELECT state_id INTO :lineage_id FROM sde.versions WHERE owner = 'SDE' AND name = 'DEFAULT';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    SELECT lineage_name INTO :lineage_name FROM sde.states WHERE state_id = :lineage_id;
  3  END;
  4  /

PL/SQL procedure successfully completed.

3. Execute the SQL statement to report the table's statistics. Notice the Primary Overhead's table registration_id value, 67, must be specified as the adds table (a67) and the deletes table (d67) in the SQL statement.
   • To discover the number of changes in the adds table, execute the following statement:

SQL> SELECT
  2   (SELECT COUNT(*) FROM admin.a67 
  3    WHERE sde_state_id IN
  4      (SELECT lineage_id FROM sde.state_lineages
  5       WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",
  6     ROUND((SELECT COUNT(*) FROM admin.a67
  7            WHERE sde_state_id IN
  8              (SELECT lineage_id FROM sde.state_lineages
  9               WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))
 10    /(SELECT COUNT(*) FROM admin.a67) * 100, 2) AS "PERCENTAGE OF ROWS", 
 11    (SELECT COUNT(*) FROM admin.a67) AS "TOTAL ROWS"
 12  FROM dual;

NUMBER OF ROWS PERCENTAGE OF ROWS TOTAL ROWS
-------------- ------------------ ----------
         17413              26.78      65032

• • To generate the same information for the deletes table, execute the following statement:

SQL> SELECT
  2   (SELECT COUNT(*) FROM admin.d67 
  3    WHERE deleted_at IN
  4      (SELECT lineage_id FROM sde.state_lineages
  5       WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id)) AS "NUMBER OF ROWS",
  6     ROUND((SELECT COUNT(*) FROM admin.d67
  7            WHERE deleted_at IN
  8              (SELECT lineage_id FROM sde.state_lineages
  9               WHERE lineage_name = :lineage_name AND lineage_id <= :lineage_id))
 10    /(SELECT COUNT(*) FROM admin.d67) * 100, 2) AS "PERCENTAGE OF ROWS", 
 11    (SELECT COUNT(*) FROM admin.d67) AS "TOTAL ROWS"
 12  FROM dual;

NUMBER OF ROWS PERCENTAGE OF ROWS TOTAL ROWS
-------------- ------------------ ----------
         16361              27.09      60384