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

Last updated on FEBRUARY 08, 2017

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

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