FAQ: What Selectivity Value is Used by the Oracle Optimizer When a St

Answer

If either of the tables specified in a spatial relational operator (st_intersects, st_within, st_overlaps, etc.) do not have table statistics (meaning there will be no values present in the ArcSDE sde.st_geometry_index table for the num_rows attribute), then the value for selectivity returned by the st_domain_stats type to the Oracle optimizer is 1.

A selectivity value of 1 indicates that all rows in the table are candidates to satisfy the predicate filter. A large value for selectivity increases the cost of using the spatial index and causes the Oracle optimizer to lean towards selecting a full table scan for the execution plan verses using the spatial index.

To avoid this behavior, always ensure both tables involved in the operator for the query have Oracle statistics.

Article ID: 000010485

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

What selectivity value is used by the Oracle optimizer when a st_geometry attribute does not have statistics?

Answer

Receive notifications and find solutions for new or common issues

Discover more on this topic