English

FAQ: When are statistics gathered for st_geometry attributes and st_spatial_index indexes in Oracle?

Question

When are statistics gathered for st_geometry attributes and st_spatial_index indexes in Oracle?

Answer

When ArcGIS analyzes an objectclass that uses st_geometry as its spatial type or when a user gathers table or index statistics in Oracle using the dbms_stats procedure, Oracle explicitly calls the st_domain_stats type to gather additional statistics for the st_geometry attribute and st_spatial_index index.

The statistics gathered for an st_geometry and index are stored in the sde.st_geometry_index table. Statistics are only gathered when a spatial index is present.

The time required to gather statistics is dependent upon the size of the table being analyzed and if a spatial index is present. Statistics should always be gathered after creating a spatial index.

If the number of rows in the table is less than 2,500,000 rows, the entire table and spatial index is analyzed, no sampling occurs.

If the number of rows in the table is greater than 2,500,000 rows, then the table and spatial index are sampled. The amount of sampling performed is determined by the session's setting for the constant DBMS_STATS.AUTO_SAMPLE_SIZE. Refer to Oracle's documentation on the dbms_stats program in the book 'PL/SQL Packages and Types Reference'.

When gathering statistics, the spatial index's clustering factor, blevel, density and leaf blocks are all gathered and persisted in the sde.st_geometry_index table for the specified table being analyzed.

Statistics should always be gathered and maintained based upon the dynamics of the data.