2 Replies Latest reply on Mar 25, 2014 12:56 AM by user1287681

    sdo_within_distance optimization


      I am using srid 4326 and am trying to optimize a spatial query that uses sdo_within_distance. The query has to compare 20 million geometries against 10 million geometries and return pairs that are with 50 meters of each other. I know that sdo_within_distance is not suitable for spatial joins so the query has to do a nested loop join. I am trying to use materialized in line views with parallel to pipeline the results and that has improved performance somewhat. Short of this are there any recommendations for improving sdo_within_distance in geodetic srids when there are large number of geometries to compare.


      With a as


      select /*+ materialize */ road_id, geom

      from roads

      ), b as


      select /*+ materialize */ park_id, geom

      from parks


      select /*+ ordered index(a roads_sidx) parallel(8) */ road_id, park_id

      from b,a

      where sdo_within_distance(a.geom,b.geom,'distance=50 meter') = 'TRUE'