FAQ: How is Selectivity Calculated for Relational Operators in Oracle with St

Answer

When the Oracle optimizer parses a SQL statement containing an st_geometry relational operator (st_contains, st_crosses, st_equals, st_intersects, st_overlaps, st_relate, st_touches, and st_within), the optimizer calls the st_domain_stats type to obtain a value for selectivity between the tables participating in the relation.

Selectivity is defined as the fraction of rows in a table that meet the conditions of the predicate filter. For example, the spatial relational operator st_within(states.shape, roads.shape) = 1 is a predicate asking to return all the roads that are in the specified states. The selectivity is the fraction of rows from roads table that meet the condition of the predicate.

The st_domain_stats type is responsible for calculating the selectivity of all spatial relational operators. For the st_domain_stats type to return an accurate selectivity value to the optimizer, it requires statistics to have been collected for the tables specified in the predicate filter.

For example, if the predicate filter is
st_within(a.shape, b.shape) = 1, the st_domain_stats type calculates the selectivity using the following formula:

Selectivity = ((a.shape table's number of rows - a.shape table's number of null values) / a.shape table's number of rows) * ((b.shape table's number of rows - b.shape table's number of null values) / b.shape table's number of rows) / GREATEST(a.shape table's number of rows - a.shape table's number of null values, b.shape table's number of rows - b.shape table's number of null values)

If either the a.shape or b.shape input is not from a table but a fixed st_geometry object (st_within(a.shape,st_geometry)), the formula for calculating selectivity becomes

Selectivity := CEIL(density of the table * .1)/(number of rows - number of nulls) * 100

where the value for density is the average number of features per grid cell from the input table's spatial index.

The values used as input for deriving the selectivity between the a.shape and b.shape attributes are obtained from the sde.st_geometry_index table.

If the relational operator predicate condition is 0, for example, st_within(a.shape,b.shape) = 0, then the derived value for selectivity is 100 minus the calculated selectivity value from the above equations.

If the a.shape or b.shape table does not contain statistics, then the value for selectivity defaults to 1 when the predicate filter condition equals 1 and 99 when the condition equals 0.

Article ID:000009234

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

How is selectivity calculated for relational operators in Oracle with st_geometry attributes?

Answer

Discover more on this topic