FAQ: What selectivity value is used by the Oracle optimizer when a st_geometry attribute does not have statistics?
What selectivity value is used by the Oracle optimizer when a st_geometry attribute does not have statistics?
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.