FAQ: How Does the Oracle Optimizer Cost the St_spatial

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

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Oracle Documentation

How does the Oracle optimizer cost the st_spatial_index when building an execution plan?

Answer

Related Information

Discover more on this topic