Is This Content Helpful?
We're glad to know this article was helpful.
How does the Oracle optimizer cost the st_spatial_index when building an execution plan?
When the Oracle optimizer parses a SQL statement containing a spatial relational operator (st_envintersects, st_intersects, st_contains, st_within, etc.), the optimizer calls on the ArcSDE type st_domain_stats to return information about the CPU and I/O costs for using the spatial index.
When the optimizer calls the function to calculate the costs, it uses the selectivity of the predicate, which was calculated in the previous step by the st_domain_stats type as input.
The st_domain_stats type queries the sde.st_geometry_index table to obtain statistics about the spatial index. Using the input selectivity value and the statistics for the domain index, the values are then applied in the following formulas for calculating the CPU and I/O costs:
cpu cost = (dbms_odci.estimate_cpu_units(.00005) * 1000) *
((selectivity/100) * (number_rows - number_nulls))
io cost = index_blevel + CEIL(number_leaf_blocks * (selectivity/100)) +
CEIL(clustering_factor * (selectivity/100))
A full table scan is the base line cost of all operations.