FAQ: What is the Correct Order for the St_geometry Relational Operator Input Arguments in

Answer

Specifying the correct argument order for the st_geometry relational operators (st_contains, st_crosses, st_equals, st_intersects, st_overlaps, st_relate, st_touches and st_within) is critical to ensure optimal query performance.

A case where the performance difference will be significant is when the less selective geometry is set as the second geometry argument in the operator - st_intersects (b.shape, a.shape) verses st_intersects (a.shape, b.shape). For example, if the objective is to discover all parcels that intersect a specific neighborhood, it is very important that the neighborhood's geometry is specified as the second argument, and not as the first argument for the relational operator. When specified as the second argument in the operator, the Oracle optimizer is allowed to use the single neighborhood geometry as the input to search the parcels spatial index. The query below demonstrates the neighborhood's geometry (b.shape) specified as the second argument.

Code:
SQL> SELECT COUNT(*) FROM parcels a, neighborhoods b 
  2  WHERE b.name = 'CLIFTON' AND
  3  sde.st_intersects(a.shape, b.shape) = 1

If the query was written with the a.shape and b.shape reversed,

Code:
SQL> SELECT COUNT(*) FROM parcels a, neighborhoods b 
  2  WHERE b.name = 'CLIFTON' AND
  3  sde.st_intersects(b.shape, a.shape) = 1

then each parcel would be the input geometry used to search the neighborhood's spatial index, and the time required to execute the query would be significantly longer.

Another case to be aware of is when using a constructor function as an input geometry. It should never be in the first argument position. The following SQL statement demonstrates passing a st_geometry constructor function as the first argument to the st_intersects operator.

Code:
SQL> SELECT road_name FROM roads
   2  WHERE sde.st_intersects(sde.st_geometry(), roads.shape) = 1;

To satisfy this query, the database must read each roads.shape geometry and then compare the relation between the geometries.

If the st_geometry object is defined in the second argument position,

Code:
SQL> SELECT road_name FROM roads
   2  WHERE sde.st_intersects(roads.shape, sde.st_geometry()) = 1;

then the roads spatial index can be used as the access path for identifying a subset of roads geometries to compare with the geometry from the constructor function.

If neither of the table's geometry attributes that the arguments reference have a spatial index, then the input order is irrelevant. Every geometry will be compared.

Article ID:000010282

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

What is the correct order for the st_geometry relational operator input arguments in Oracle?

Answer

Discover more on this topic