SDO_RELATE Slow When Combining Query With Other Btree Indexed Columns (Doc ID 1908233.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.2 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.

Symptoms

The performance of the SDO_RELATE query when combined with other conditions is slow

The plan shows the BITMAP CONVERSION TO ROWIDS

select ID, EVENTTYPE, LOCATION
from
cjl_events
where
isopen = 'T'
and eventtype = '4Q'
and sdo_relate(LOCATION,
  mdsys.sdo_geometry(2001,2193,NULL,
  mdsys.sdo_elem_info_array(1,1003,3),
  mdsys.sdo_ordinate_array(1084000,4722000,2092000,6234000)),
  'mask=anyinteract querytype=window') = 'TRUE';


Elapsed: 00:00:01.96

Execution Plan
----------------------------------------------------------
Plan hash value: 772467088

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 8 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | CJL_EVENTS | 1 | 35 | 8 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP AND | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 5 | INDEX RANGE SCAN | CJL_EVENTS_ISOPEN | 403 | | 3 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 7 | SORT ORDER BY | | | | | |
|* 8 | DOMAIN INDEX (SEL: 0.100000 %)| CJL_EVENTS_SPIDX | 403 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("EVENTTYPE"='4Q')
  5 - access("ISOPEN"='T')
  8 - access("MDSYS"."SDO_RELATE"("LOCATION","MDSYS"."SDO_GEOMETRY"(2001,2193,NULL,"MDSYS"."SDO_
  ELEM_INFO_ARRAY"(1,1003,3),"MDSYS"."SDO_ORDINATE_ARRAY"(1084000,4722000,2092000,6234000)),'mask=a
  nyinteract querytype=window')='TRUE')

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