I am calculating distance between two 2 points (using their gps co-ordinates) from the below query (Query 1). One column of my table stores the geo_location of point1 and location of point2 is dynamically provided into bind variable. The query works fine and gives me the exact distance between point1 and point2. However I see a high CPU utilization on the DB server (from 60% upto 95% at times) whenever this query executes. When I tried to scan the AWR report, I found that there is a another query (Query 2 below) that gets triggered internally and this query scans every row (for every execution of Query1) in the table irrespective of where clause (I am looking for rows with a specific text only for which the distance must be calculated) ! I have tried giving the text search as a subquery (Query3) so this sdo_nn runs only on this subset but in vain. If there are 15k records in the table and if Query 1 is executed 2k times then the number of executions of Query 2 from AWR is 15k * 2k times ! I am using Oracle 11g. Request your assistance. Am I properly implementing the sdo_nn functionality? Is there any way that I can implement to avoid Query2?
Query1:
SELECT geo_location, Sdo_Nn_Distance (1) distance FROM my_table a WHERE SDO_NN(a.GEO_LOCATION, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (:7 , :8 , NULL), NULL, NULL), 1) = 'TRUE') AND (contains(a.col1, :9 ) > 0 ORDER BY DISTANCE;
Bind variable in above query represents:
:7 longitude inputs
:8 latitude inputs
:9 searchtext inputs
Query2:
SELECT a."GEO_LOCATION" FROM my_table a where a.rowid=:rid
Query3:
SELECT geo_location, Sdo_Nn_Distance (1) distance FROM my_table a WHERE SDO_NN(a.GEO_LOCATION, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (:7 , :8 , NULL), NULL, NULL), 1) = 'TRUE') AND a.col1 in (select col1 from my_table where (contains(a.col1, :9 ) > 0) ORDER BY DISTANCE;
