Spatial Query On LOCAL Partitioned Table Does Not Return The Requested Rows (Doc ID 2013063.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Spatial query on LOCAL partitioned table does not return the requested rows

After inserting rows into a partitioned table, we try to query these rows based on a spatial operator (sdo_filter).
This query does not return any rows.

The execution plan states that the spatial index is used.

When rewriting this query to use the relate function the query returns the expected number of rows.

So far, the issue is resolved by rebuilding the spatial index for the affected partition(s), i.e. the partitions that received new rows when inserting data.

Since our application has not received any signal / exception about the state of the spatial index, it is working with incomplete data.
As a consequence the final result of all spatial processing is wrong, which is a major loss of service.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms