FAQ: How is selectivity calculated for the ST_EnvIntersects operator?
How is selectivity calculated for the ST_EnvIntersects operator?
When using the ST_EnvIntersects operator, the ST_Domain_Stats type calculates the selectivity of the ST_Geometry attribute by using the input geometry or envelope and the indexes statistics from the SDE.ST_Geometry_Index table.
Selectivity is very important to Oracle's optimizer and used in identifying how selective an index is and therefore determines the cost of using the index verses performing a full table scan.
Take for example the following two queries which use the ST_EnvIntersects operator:
SQL> SELECT owner, address, pin
SQL> SELECT p.owner, p.address, p.pin
FROM parcels p, zipcodes z
WHERE st_envintersects(p.shape,z.shape)=1 AND z.id = 92373;
The two queries are using the ST_EnvIntersects operator to return a set of parcels whose envelopes intersect an envelope, query 1, and an input geometry's envelope, query 2.
Based upon the envelope or the value of the input geometry's envelope, the ST_Domain_Stats type calculates the ST_Geometry's selectivity with the following formula:
((maxx - minx) * (maxy - miny)) * (density/(grid1 * grid1))/(num_rows -null_cnt) * 100
The values for maxx, minx, maxy, miny are derived from either the input envelope or the envelope of the input geometry, and the values for density, grid1, num_rows, null_cnt are all obtained from the SDE.ST_Geometry_Index table for the given ST_Geometry attribute.
Using the first query as an example, the selectivity for the parcels based upon the input envelope would equal:
((20 - 10) * (20 - 10)) * (290.10/(10 * 10))/(178321 - 0) * 100
This returns a selectivity value of .162684. The optimizer uses selectivity to represent the fraction of rows which is returned by the statement.
The number of rows returned by the statement, 290 in above case, is the cardinality for the statement or predicate. Cardinality is simply calculated by multiplying the number of rows in the table by the selectivity:
178,321 parcels * (.162684/100) = 290 rows (rounded).
Selectivity must be a value between 0 and 100.
If the operator is requesting not equal, ST_EnvIntersects(a.shape,b.shape) = 0, then the value for selectivity is the inverse or 100 - selectivity. Using the previous example, the value for selectivity would be 100 - .162684 = 99.837316. The cardinality based upon the selectivity 99.837316 returns 178612 parcels.