Slow Query Using Sdo_filter other predicates and Joining two tables (Doc ID 2037480.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

Slow performance in query using SDO_FILTER and joining two tables.

The spatial index is used and the performance is bad.
If we use hint NO_INDEX to avoid the spatial index, then the performance is good.


Here is the scenario

SMALL_TABLE with 20 rows has SDO_GEOMETRY
BIG_TABLE with 2,000,000 rows also with an SDO_GEOMETRY column

The SELECT:

select
  /*+ ORDERED */
  BT.POLIGON
from
  SMALL_TABLE ST,
  BIG_TABLE BT
WHERE
  BT.DATE_COL BETWEEN to_date('20150319', 'yyyymmdd') and to_date('20150518', 'yyyymmdd')
and ST.ID in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
and SDO_FILTER(BT.POLIGON, ST.poligon) = 'TRUE'
and BT.COLX = 'E1';

This select is running slow because it is using the spatial index
The problem here of using the spatial index is that each ID from the SMALL_TABLE for example ID=2 will map (join) hundreds of thousands of rows with BIG_TABLE
It is not very selective thus spending too much time.

The other conditions are very selective but they are no seen by the optimizer when using a spatial operation

The spatial index is used because the optimizer thinks that the ID=2 or ID=3 may return a few rows from BIG_TABLE but this is a special case where it returns too many rows.

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