Slow Query Using Sdo_filter other predicates and Joining two tables
Last updated on FEBRUARY 08, 2017
Applies to:Oracle Spatial and Graph - Version 22.214.171.124 and later
Information in this document applies to any platform.
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
/*+ ORDERED */
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.
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