English

Problem: 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;

Dependent on the number of rows in the cities table, the time it requires to execute the query can be substantial.

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

Because the st_contains operator is unable to leverage the spatial index of the table specified in the second position, the solution is to use the inverse of contains, which is within.

The st_within operator performs the exact same relational comparison between the two specified geometries as the st_contains operator. The only difference is that the order of the arguments must be specified correctly. The first argument is always the geometry which is 'within' the specified second geometry.

For example, instead of writing a query to return all the cities contained by a given 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;

To ensure optimal performance and leverage the spatial index, write the query as cities within states.

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;

Now the Oracle optimizer is able to select an execution plan where the input states shape is used as the filter for the cities spatial index.