Re: Interesting Issue related to sql result

​​​
Hi All,

The issue is solved.

The Oracle version is : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

The issue is not related to the query process, this is a specific issue of the Spatial queries, according this text (in red):

" For geodetic data (such as data identified by longitude and latitude coordinates), the tolerance value is a number of meters. For example, a tolerance value of 100 indicates a tolerance of 100 meters. The tolerance value for geodetic *data should not be smaller than 0.05 (5 centimeters), *and in most cases it should be larger. Spatial uses 0.05 as the tolerance value for geodetic data if you specify a smaller value with the following functions: SDO_GEOM.RELATE, SDO_GEOM.SDO_DIFFERENCE, SDO_GEOM.SDO_INTERSECTION, SDO_GEOM.SDO_UNION, and SDO_GEOM.SDO_XOR; for other functions, Spatial uses the smaller tolerance value that you specify."​

There was an geometry validated as TRUE when using 0.001 tolerance, but 0.05 is the minimum value.
We validated the geometry with 0.05 tolerance, adjusted the wrong geometry and got the expected result in both queries.

Thansks for all answers.

Regards
Eriovaldo

2018-04-06 5:21 GMT-03:00 Stefan Koehler <contact_at_soocs.de>:

> Hello Eriovaldo,
> unfortunately you have not provided either your exact Oracle version or
> the full execution plan (especially column projection information) ... so
> this is a wild guess but as this wrong result set seems to be related to
> the FETCH data volume - please just try the following:
>
> SQL> alter session set "_rowsets_enabled"=FALSE;
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Eriovaldo Andrietta <ecandrietta_at_gmail.com> hat am 5. April 2018 um
> 21:25 geschrieben:
> >
> > HI,
> >
> > Does anyone knows ​what happens in this cenary :
> >
> > I have a query like this.
> >
> > SELECT /*+ parallel (area_control,6)
>
> > parallel (lot_f,6)
> > */
> > [lot_f.id](http://lot_f.id),
> > lot_f.lot_id
> > FROM op_serving_area_f area_control,
> > lot_f lot_f,
> > lot,
> > addr_blocked_cfg,
> > op_serving_area area_c,
> > op_serving_area_wcenter
> > WHERE sdo_relate(lot_f.geometry,
> > area_control.geometry,
> > 'mask=INSIDE+COVEREDBY') = 'TRUE'
> > AND SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(area_control.geometry,
> 0.001) = 'TRUE'
> > AND area_control.op_serving_area_id = [area_c.id](
> http://area_c.id)
> > AND area_c.area_type = 2
> > AND [lot.id](http://lot.id) = lot_f.lot_id
> > AND lot.addr_blocked_cfg_id = [addr_blocked_cfg.id](http://
> addr_blocked_cfg.id)(+)
> > AND op_serving_area_wcenter.op_serving_area_id = [area_c.id](
> http://area_c.id)
> > and lot_f.lot_id = 218692
> > /
> >
> > When I run it as is above it returns to me the line :
> >
> >
> > 5679907 218692
> >
> >
> > ​If I change it in order to return all lines, ​ changing only it :
> >
> >
> > -- and lot_f.lot_id = 218692
> >
> > ​The line does not return in the result.
> >
> > Any experience with cenary like this ?
> >
> > Regards
> > Eriovaldo
>

--
http://www.freelists.org/webpage/oracle-l