My Oracle Support Banner

The Optimizer In 11.2.0.3 Chooses Incorrect Execution Plan For A Query Using SDO_FILTER (Doc ID 2022091.1)

Last updated on JULY 16, 2019

Applies to:

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

Symptoms

The optimizer in 11.2.0.3 chooses incorrect execution plan for a query using SDO_FILTER and another condition in the where clause. This is fixed in 11.2.0.4 and higher releases.

The workaround in note:

  Slow Performance when using SDO_RELATE/SDO_FILTER (Doc ID 1268383.1)

was attempted but did not resolve the problem.

The workaround using the hint NO_INDEX in note:

  Slow Query Using SDO_FILTER The Optimizer Choosing Bitmap Conversion To Rowids (Doc ID 1189964.1)

was attempted but did not help

This is the query

SELECT id FROM tab3 WHERE id=7898
AND
SDO_FILTER(
 SPATIAL_DATA,
 SDO_GEOMETRY(
  2003, -- two-dimensional polygon
  8307, -- SRID for WGS-84 longitude/latitud
  NULL,
  SDO_ELEM_INFO_ARRAY(1, 1003, 1),
  SDO_ORDINATE_ARRAY(
  -94.8338234424591,39.11655560213989,
  -94.83394682407379,39.11618478057385,
  -94.83402192592621,39.11574920989278,
  -94.83404874801636,39.11532540880791,
  -94.83400046825409,39.114819198615976,
  -94.83385562896729,39.11417171516305,
  -94.83353912830353,39.11296502557993,
  -94.83324944972992,39.11248823034213,
  -94.8328846693039,39.11213504660467,
  -94.83245015144348,39.11187604407223,
  -94.83188152313232,39.1116700186508,
  -94.8313558101654,39.1115581760269,
  -94.8308676481247,39.11156994894302,
  -94.83044385910034,39.111611154134025,
  -94.82995569705963,39.111711223783246,
  -94.82944071292877,39.111934908367736,
  -94.82903838157654,39.11224688831305,
  -94.82870578765869,39.11257652599992,
  -94.82847511768341,39.11292382118056,
  -94.82679605484009,39.11707357185418,
  -94.82670485973358,39.117479704535015,
  -94.82674241065979,39.118021211134604,
  -94.8269248008728,39.11850974069151,
  -94.82704818248749,39.118739289554505,
  -94.82735931873322,39.119121869330876,
  -94.82782065868378,39.119427931656176,
  -94.82835710048676,39.11965747752762,
  -94.8288881778717,39.119781078840944,
  -94.82943534851074,39.11981639346204,
  -94.82993960380554,39.11977519306902,
  -94.83050286769867,39.11964570596266,
  -94.83092129230499,39.119422045854755,
  -94.83125925064087,39.11921015667656,
  -94.83244478702545,39.11835670770069,
  -94.8330295085907,39.11783874741924,
  -94.83343183994293,39.11730901137464,
  -94.83371078968048,39.11684401756424,
  -94.8338234424591,39.11655560213989
  )
 )
) = 'TRUE';


Execution Plan
----------------------------------------------------------
Plan hash value: 2644375476

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 2 (0)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS | | 1 | 57 | 2 (0)| 00:00:01 |
| 2 | BITMAP AND | | | | | |
| 3 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 4 | SORT ORDER BY | | | | | |
|* 5 | DOMAIN INDEX | TAB3_SPATIAL_IDX | 1 | | 0 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | SYS_C0011047 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

  5 - access("MDSYS"."SDO_FILTER"("SPATIAL_DATA","MDSYS"."SDO_GEOMETRY"(2003,8307,NULL,"SDO
  _ELEM_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"((-94.8338234424591),39.11655560213989,(-94.
  83394682407379),39.11618478057385,(-94.83402192592621),39.11574920989278,(-94.83404874801636
  ),39.11532540880791,(-94.83400046825409),39.114819198615976,(-94.83385562896729),39.11417171
  516305,(-94.83353912830353),39.11296502557993,(-94.83324944972992),39.11248823034213,(-94.83
  28846693039),39.11213504660467,(-94.83245015144348),39.11187604407223,(-94.83188152313232),3
  9.1116700186508,(-94.8313558101654),39.1115581760269,(-94.8308676481247),39.11156994894302,(
  -94.83044385910034),39.111611154134025,(-94.82995569705963),39.111711223783246,(-94.82944071
  292877),39.111934908367736,(-94.82903838157654),39.11224688831305,(-94.82870578765869),39.11
  257652599992,(-94.82847511768341),39.11292382118056,(-94.82679605484009),39.11707357185418,(
  -94.82670485973358),39.117479704535015,(-94.82674241065979),39.118021211134604,(-94.82692480
  08728),39.11850974069151,(-94.82704818248749),39.118739289554505,(-94.82735931873322),39.119
  121869330876,(-94.82782065868378),39.119427931656176,(-94.82835710048676),39.11965747752762,
  (-94.8288881778717),39.119781078840944,(-94.82943534851074),39.11981639346204,(-94.829939603
  80554),39.11977519306902,(-94.83050286769867),39.11964570596266,(-94.83092129230499),39.1194
  22045854755,(-94.83125925064087),39.11921015667656,(-94.83244478702545),39.11835670770069,(-
  94.8330295085907),39.11783874741924,(-94.83343183994293),39.11730901137464,(-94.833710789680
  48),39.11684401756424,(-94.8338234424591),39.11655560213989)))='TRUE')
  7 - access("ID"=7898)

Expecting:

Execution Plan
----------------------------------------------------------
Plan hash value: 3151648667

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TAB3 | 1 | 57 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0011047 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

  1 - filter("MDSYS"."SDO_FILTER"("SPATIAL_DATA","MDSYS"."SDO_GEOMETRY"(2003,8307,N
  ULL,"SDO_ELEM_INFO_ARRAY"(1,1003,1),"SDO_ORDINATE_ARRAY"((-94.8338234424591),39.1165
  5560213989,(-94.83394682407379),39.11618478057385,(-94.83402192592621),39.1157492098
  9278,(-94.83404874801636),39.11532540880791,(-94.83400046825409),39.114819198615976,
  (-94.83385562896729),39.11417171516305,(-94.83353912830353),39.11296502557993,(-94.8
  3324944972992),39.11248823034213,(-94.8328846693039),39.11213504660467,(-94.83245015
  144348),39.11187604407223,(-94.83188152313232),39.1116700186508,(-94.8313558101654),
  39.1115581760269,(-94.8308676481247),39.11156994894302,(-94.83044385910034),39.11161
  1154134025,(-94.82995569705963),39.111711223783246,(-94.82944071292877),39.111934908
  367736,(-94.82903838157654),39.11224688831305,(-94.82870578765869),39.11257652599992
  ,(-94.82847511768341),39.11292382118056,(-94.82679605484009),39.11707357185418,(-94.
  82670485973358),39.117479704535015,(-94.82674241065979),39.118021211134604,(-94.8269
  248008728),39.11850974069151,(-94.82704818248749),39.118739289554505,(-94.8273593187
  3322),39.119121869330876,(-94.82782065868378),39.119427931656176,(-94.82835710048676
  ),39.11965747752762,(-94.8288881778717),39.119781078840944,(-94.82943534851074),39.1
  1981639346204,(-94.82993960380554),39.11977519306902,(-94.83050286769867),39.1196457
  0596266,(-94.83092129230499),39.119422045854755,(-94.83125925064087),39.119210156676
  56,(-94.83244478702545),39.11835670770069,(-94.8330295085907),39.11783874741924,(-94
  .83343183994293),39.11730901137464,(-94.83371078968048),39.11684401756424,(-94.83382
  34424591),39.11655560213989)))='TRUE')
  2 - access("ID"=7898)

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.