Poor performance when executing a SQL query using the st_contains operator in Oracle

Description

Instructions provided describe how to use the st_within operator to perform the exact same relational comparison between two specified geometries as the st_contains operator.

The following example demonstrates executing a query to return all cities contained by a specified state.

Code:
SQL> SELECT cities.name
  2 FROM cities, states
  3 WHERE states.name = 'New York'
  4 AND sde.st_contains(states.shape,cities.shape) = 1;

Cause

The cause of the problem is the Oracle optimizer is unable to use the spatial index for the geometry specified in the second position of the operator.

By definition the st_contains operator must evaluate the first and second argument in the order specified, meaning geometry 1 contains geometry 2, for example, st_contains(states,cities).

Oracle's Data Cartridge support for extensible types, operators, indexes and optimizer only supports the use of a domain index on arguments in the first position of an operator. This is a limitation that cannot be overcome by any custom behavior by the type implementor.

Therefore, the st_contains operator will always result in a full table scan against the table for the second position's geometry argument. In the above example, this means for every input state shape, it must be processed as a filter against every city shape to determine the spatial relationship. Each relational comparison is expensive in terms of resources and time consumed. As the number of rows increases in the table (cities) the cost of executing the query increases.

Solution or Workaround

Code:
SQL> SELECT cities.name
  2  FROM cities, states
  3  WHERE states.name = 'New York'
  4  AND sde.st_contains(states.shape,cities.shape) = 1;

Code:
SQL> SELECT cities.name
  2  FROM cities, states
  3  WHERE states.name = 'New York'
  4  AND sde.st_within(cities.shape,states.shape) = 1;