Search code examples
sqloracle-databaseoracle-spatial

Can Oracle be forced to use the spatial index for sdo_filter in combination with an or clause?

I have a table MY_TABLE in oracle with a Spatial index MY_IDX and about 22000 rows. The following query runs in less than ~500 ms and returns ~2600 results.

SELECT /*+ INDEX (MY_TABLE MY_IDX) */ ID,GEOM,LABEL FROM MY_TABLE
where (
 (sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(-180.0,-48.0,-67.0,32.0)),
 'querytype=WINDOW')='TRUE')
);

When I add an "OR" clause with another spatial filter, the query takes ~30 seconds to run, consuming vastly more CPU than it should:

SELECT /*+ INDEX (MY_TABLE MY_IDX) */ ID,GEOM,LABEL FROM MY_TABLE
where (
 (sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(-180.0,-48.0,-67.0,32.0)),
 'querytype=WINDOW')='TRUE')
 OR
 (sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(157.0,-48.0,180.0,32.0)),
 'querytype=WINDOW')='TRUE')
);

The explain plans of the queries are very different - the first shows table access is "BY INDEX ROWID", where as the second is "FULL". Is there a way I can get the second query to perform in a manner similar to the first?

v$version returns:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

On a side note, a different db running oracle enterprise edition produces a plan in which the index is used and the results merged. Can this be done with standard edition?

Solution

  • Refractoring the query using an inner query and a union all seemed to force oracle to use the indexes as expected:

    SELECT ID,GEOM,LABEL FROM MY_TABLE
WHERE ID IN (
 (SELECT ID FROM MY_TABLE WHERE sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(-180.0,-48.0,-67.0,32.0)),
 'querytype=WINDOW')='TRUE')
 UNION ALL
 (SELECT ID FROM MY_TABLE WHERE sdo_filter(GEOM, mdsys.sdo_geometry(2003,8307,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(157.0,-48.0,180.0,32.0)),
 'querytype=WINDOW')='TRUE')
);