2 Replies Latest reply: Mar 24, 2014 7:56 PM by user1287681 RSS

    sdo_within_distance optimization

    user1287681

      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'