5 Replies Latest reply on Mar 25, 2014 6:29 PM by Ying Hu-Oracle

# sdo_nn with distance parameter vs sdo_within_distance

I had a colleague ask me what the difference was between using the sdo_nn operator with a distance specified and a sdo_batch_size of 0 vs using sdo_within_distance. I explained the high level difference but was unable to find any documentation that was more in depth. Does anyone know if there is any documentation that explains how sdo_nn with a distance parameter works compared to using sdo_within_distance

Thanks

Bobby

• ###### 1. Re: sdo_nn with distance parameter vs sdo_within_distance

Have no deep theoretical knowledge about this, so tested both versions on a table with 3 mio rows (ORA 11.2.0.3.).

Found no difference in time, execution plan, costs and of course not in results .

• ###### 2. Re: sdo_nn with distance parameter vs sdo_within_distance

SDO_NN can return rows in ascending order of distance, while SDO_WITHIN_DISTANCE does not.

• ###### 3. Re: sdo_nn with distance parameter vs sdo_within_distance

They are completely different.

sdo_nn returns the N nearest objects, whatever their distance. You can further limit the results using the distance parameter. For example if searching for the nearest gas stations, you probably only want those that are within some reasonable distance.

sdo_within_distance returns *all* objects that are within a set distance.

• ###### 4. Re: sdo_nn with distance parameter vs sdo_within_distance

Albert, if you don't limit the rows returned by sdo_nn then it can give the same results as sdo within distance. For examples find the nearest gas stations within 5 miles is the same as find all gas stations within 5 miles unless you care about proximity and limit the results to the closest (x) gas stations. We tested it and performance seems to be the same with both so we are using within distance since it is more transparent and we do not care about proximity. Yhu, your point about distance ordering make sense though could I not do the same with sdo_distance?

• ###### 5. Re: sdo_nn with distance parameter vs sdo_within_distance

If you don't use SDO_NN, you may do something like:

select ...

from table a

where sdo_wthin_distance(a.geom :geom, 'distance=...') = 'TRUE'

order by sdo_geom.sdo_distance(a.geom, :geom, :tolerance)

The performance of the above query depends on how many rows are returned from sdo_within_distance(),

as sorting a large number of rows can be expensive.