Oracle Spatial Functions performing slow

Question

I have a table X which contains the longitude and latitude of 800k points. The SDO_GEOMETRY column in X is Y that stores the coordinates of the points as point geometry type. I have also created a spatial index for Y.

I executed a query to check how many out of those 800k points lie within 1000 metres from a specific (longitude,latitude) (specified as aa.bbbbbb,cc.dddddd) coordinate as follows in SQL Plus:
SELECT * FROM X x WHERE sdo_geom.sdo_distance(SDO_GEOMETRY(2001,8307, SDO_POINT_TYPE(aa.bbbbbb,cc.dddddd,NULL),NULL,NULL),x.Y,0.5) < 1000

My doubt is regarding the slow performance of query. The time taken is around 8 minutes and I am hoping to execute it much faster. Why is so happening? Is there any way to make the query run faster? Or am I doing something wrong? Please help. Thank You.

Albert Godfrind Albert Godfrind · Accepted Answer · 2017-08-11T20:49:13

The proper way to do this kind of query is to use the SDO_WITHIN_DISTANCE() operator that uses the spatial index. As written, you are computing the distance between each and every of the 800000 points in your table and the comparison point.

SELECT * 
FROM X x 
WHERE sdo_within_distance(
  x.Y,
  SDO_GEOMETRY(2001,8307,SDO_POINT_TYPE(aa.bbbbbb,cc.dddddd,NULL),NULL,NULL),
  'distance=1000 unit=m'
) = 'TRUE';

Oracle Spatial Functions performing slow

1 Answers