Frequently asked question
How does the Oracle optimizer cost the st_spatial_index when building an execution plan?
Answer
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:
Code:
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))
The number_rows value equals the number of rows in the table, number_nulls equals the number of NULL st_geometry attributes in the table, the index_blevel is the spatial index's depth, the number_leaf_blocks equals the number of leaf blocks for the spatial index, and the clustering_factor represents the indexes clustering factor in relationship to the table data.
The values returned by the sde st_domain_stats type are then used by Oracle's optimizer in determining if the cost of using the spatial index is less than or greater than the cost of performing a full table scan.
Note:
A full table scan is the base line cost of all operations.
For additional information on the dbms_odci package see Oracle's documentation.
Article ID: 000009229
Software:
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Related Information
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App