Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, asg.

Asked: July 13, 2016 - 7:47 am UTC

Last updated: October 30, 2018 - 6:23 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi Tom

I am calculating distance between two 2 points (using their gps co-ordinates) from the below query (Query 1). One column (name geo_location of sdo_geometry datatype where spatial index is created) in my table stores the geometric location of point1 and location of point2 co-ordinates is dynamically provided into bind variable during query execution. 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 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) as 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 variables in above query:
:7 represents the longitude
:8 represents the latitude
:9 represents the search text


Query2:
SELECT a."GEO_LOCATION" FROM my_table a where a.rowid=:rid;

and Connor said...

I've reproduced your observation with a test case

SQL> create table T
  2    ( geo_location SDO_GEOMETRY,
  3      col1 varchar2(100)
  4    );

Table created.

SQL>
SQL>
SQL> insert into t
  2  with pts as
  3   ( select trunc(dbms_random.value(-80,80)) x,
  4            trunc(dbms_random.value(-80,80)) y,
  5            table_name
  6     from dba_tables
  7     where rownum <= 1000
  8    )
  9  select SDO_GEOMETRY('POINT('||x||' '||y||')', 8307), table_name
 10  from pts;

1000 rows created.

SQL> INSERT INTO user_sdo_geom_metadata VALUES (
  2    'T',
  3    'GEO_LOCATION',
  4    SDO_DIM_ARRAY(
  5      SDO_DIM_ELEMENT('Longitude',-180,180,0.5),
  6      SDO_DIM_ELEMENT('Latitude',-90,90,0.5)
  7    ),
  8    8307
  9  );

1 row created.

SQL> CREATE INDEX t_ix ON t (geo_location)
  2     INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Index created.

SQL> SELECT count(*)
  2  FROM t a
  3  WHERE SDO_NN(a.GEO_LOCATION, SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE (-36 , 18 , NULL), NULL, NULL), 'sdo_batch_size=100', 1) = 'TRUE' ;

  COUNT(*)
----------
      1000

1 row selected.
<code>

with the following output in the trace file

<code>
SELECT a."GEO_LOCATION" 
FROM
 "MCDONAC"."T" a WHERE a.rowid = :rid


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   1000      0.09       0.06          0         42          0           0
Fetch     1000      0.01       0.01          0       1000          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2001      0.10       0.08          0       1042          0        1000


A couple of things I would do:

a) build a simple test case like mine and send it off to Support asking for clarification if this is correct behaviour.

b) (My understanding of the) nearest neighbour queries is about reducing resultsets, whereas your query appears to be getting distance for every single row that matches the name criteria. Perhaps it would better off adding (for example) "distance=some_upper_limit" to the query ? This allows elimination of rows that are not practically of any interest and reduces the number of rowid executions.

Check out this blog post by the Spatial PM

https://blogs.oracle.com/oraclespatial/entry/tips_on_tuning_sdo_nn

Hope this helps.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Spatial Queries

asg, July 19, 2016 - 8:28 am UTC

Thank you for the notes from the blog!
I happened to find that it is the correct behaviour. However, setting of parameter 'layer_gtype=point' during index creation helped reduce the number of iterations of ROWID query.

High CPU Utilization

asg, August 17, 2016 - 4:25 pm UTC

Hi Chris

I read the blog and I have tried to create an Ordered table but in vain. I have provided the scripts for creating tables, indexes and inserting test data for your review. When I execute the query (provided below), it results in almost 100% CPU utilization. Kindly assist.

-- Creating table 1 (with Spatial index of interest)
CREATE TABLE LOCS_ORDERED
(LOC_SEARCH_ID NUMBER(20,0),
LOGO_ID NUMBER(15,0),
STATUS NUMBER(1,0),
GEO_LOCATION SDO_GEOMETRY
);

-- Creating table 2 to join with table 1
CREATE TABLE IL
(LOGO_ID NUMBER);

-- Creating the Spatial and Context Indexes
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('LOCS_ORDERED','GEO_LOCATION', mdsys.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONG',-180,180,0.5), MDSYS.SDO_DIM_ELEMENT('LAT',-90,90,0.5)), 8307);
COMMIT;
CREATE INDEX ORDERED_IDX1 ON locs_ordered (GEO_LOCATION) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('LAYER_GTYPE=POINT');

-- Inserting the records
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('442',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('443',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('444',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('445',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('446',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('447',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('465',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('466',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('204',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(12.14,12.258,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('1','455050','0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.467674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('2',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.468674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('468',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('4','457551','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.467784,58.468674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('5','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.466674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('6','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.465784,58.467674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('10',null,'0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.465784,58.466674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('49','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.457674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('51','455050','0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.456784,58.467674,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('441',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('469',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('470',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('529',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('530',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('531',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('532',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('503',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('448',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('463',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('522',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('524',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('526','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('527',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('528',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('467',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('426',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('482',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('484',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('521',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('523',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('425',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('501',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('502',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('533',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('481',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('483',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('452','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('453','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('464',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));
COMMIT;

-- Executing the query of interest
SELECT /*+ first_rows */
IL.LOGO_ID,
ROUND(Sdo_Nn_Distance (1),1) AS distance
FROM IMAGE_LOGO IL,
LOCS_ORDERED LOCS
WHERE LOCS.LOGO_ID = IL.LOGO_ID (+)
AND LOCS.STATUS IN (1,2)
AND Sdo_Nn(LOCS.Geo_Location, Sdo_Geometry(2001, 8307, Sdo_Point_Type (7.465784,58.466674, NULL),NULL,NULL), 'sdo_batch_size=20 distance=1000 unit=meter', 1) = 'TRUE'
ORDER BY distance;

asg, September 26, 2016 - 2:06 pm UTC

Hello Chris

Can you please assist as to why this query is consuming 100% CPU at all times?
Chris Saxon
September 27, 2016 - 12:23 am UTC

I ran this on 11.2.0.4 - I dont see the huge cpu cost ?


SQL> drop TABLE LOCS_ORDERED  purge;
drop TABLE LOCS_ORDERED  purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table IL  purge;
drop table IL  purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> CREATE TABLE LOCS_ORDERED
  2  (LOC_SEARCH_ID NUMBER(20,0),
  3  LOGO_ID NUMBER(15,0),
  4  STATUS NUMBER(1,0),
  5  GEO_LOCATION SDO_GEOMETRY
  6  );

Table created.

SQL>
SQL> -- Creating table 2 to join with table 1
SQL> CREATE TABLE IL
  2  (LOGO_ID NUMBER);

Table created.

SQL>
SQL> -- Creating the Spatial and Context Indexes
SQL> INSERT INTO USER_SDO_GEOM_METADATA VALUES ('LOCS_ORDERED','GEO_LOCATION', mdsys.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONG',-180,180,0.5), MDSYS.SDO_DIM_ELEMENT('LAT',-90,90,0.5)), 8307);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX ORDERED_IDX1 ON locs_ordered (GEO_LOCATION) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('LAYER_GTYPE=POINT');

Index created.

SQL>
SQL>
SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('442',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('443',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('444',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('445',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('446',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('447',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('465',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('466',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('204',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(12.14,12.258,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('1','455050','0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.467674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('2',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.468674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('468',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('4','457551','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.467784,58.468674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('5','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.466674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('6','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.465784,58.467674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('10',null,'0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.465784,58.466674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('49','500050','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.466784,58.457674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('51','455050','0',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(7.456784,58.467674,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('441',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('469',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('470',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('529',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('530',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('531',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('532',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('503',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('448',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('463',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('522',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('524',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('526','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('527',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('528',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('467',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('426',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('482',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('484',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('521',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('523',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('425',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('501',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('502',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('533',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('481',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('483',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('452','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('453','6351','1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> Insert into LOCS_ORDERED (LOC_SEARCH_ID,LOGO_ID,STATUS,GEO_LOCATION) values ('464',null,'1',MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL));

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','LOCS_ORDERED')

PL/SQL procedure successfully completed.

SQL>
SQL> -- Executing the query of interest
SQL> SELECT /*+ gather_plan_statistics first_rows */
  2  IL.LOGO_ID,
  3  ROUND(Sdo_Nn_Distance (1),1) AS distance
  4  FROM  IL,
  5  LOCS_ORDERED LOCS
  6  WHERE LOCS.LOGO_ID = IL.LOGO_ID (+)
  7  AND LOCS.STATUS IN (1,2)
  8  AND Sdo_Nn(LOCS.Geo_Location, Sdo_Geometry(2001, 8307, Sdo_Point_Type (7.465784,58.466674, NULL),NULL,NULL), 'sdo_batch_size=20 distance=1000 unit=meter', 1) = 'TRUE'
  9  ORDER BY distance;

   LOGO_ID   DISTANCE
---------- ----------
                 58.4
                111.4
                230.3
                251.5

4 rows selected.

SQL>
SQL> @exp

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dw1th7x7zhrf5, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics first_rows */ IL.LOGO_ID,
ROUND(Sdo_Nn_Distance (1),1) AS distance FROM  IL, LOCS_ORDERED LOCS
WHERE LOCS.LOGO_ID = IL.LOGO_ID (+) AND LOCS.STATUS IN (1,2) AND
Sdo_Nn(LOCS.Geo_Location, Sdo_Geometry(2001, 8307, Sdo_Point_Type
(7.465784,58.466674, NULL),NULL,NULL), 'sdo_batch_size=20 distance=1000
unit=meter', 1) = 'TRUE' ORDER BY distance

Plan hash value: 2430637529

------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |      4 |00:00:00.01 |     570 |      6 |       |       |          |
|   1 |  SORT ORDER BY                    |              |      1 |      1 |      4 |00:00:00.01 |     570 |      6 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS OUTER              |              |      1 |      1 |      4 |00:00:00.01 |   28 |         0 |       |       |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID    | LOCS_ORDERED |      1 |      1 |      4 |00:00:00.01 |   28 |         0 |       |       |          |
|*  4 |     DOMAIN INDEX (SEL: 0.100000 %)| ORDERED_IDX1 |      1 |        |      7 |00:00:00.01 |   27 |         0 |       |       |          |
|*  5 |    TABLE ACCESS FULL              | IL           |      4 |      1 |      0 |00:00:00.01 |    0 |         0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(("LOCS"."STATUS"=1 OR "LOCS"."STATUS"=2))
   4 - access("MDSYS"."SDO_NN"("LOCS"."GEO_LOCATION","MDSYS"."SDO_GEOMETRY"(2001,8307,"SDO_POINT_TYPE"(7.465784,58.466674,NULL),NULL,NUL
              L),'sdo_batch_size=20 distance=1000 unit=meter',1)='TRUE')
   5 - filter("LOCS"."LOGO_ID"="IL"."LOGO_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)


34 rows selected.

SQL>
SQL>


asg, September 27, 2016 - 8:39 am UTC

Thanks Connor.

We observe this issue only when there is high load. When this query is looped in a procedure some 5000 times and this procedure is executed concurrently from 10 different PL/SQL sessions, you will see the raise in DB server CPU >90% and also from AWR reports (50k executions). Request your assistance.
Connor McDonald
September 28, 2016 - 7:30 am UTC

If I run *anything* enough times with enough concurrency, I will use more and more CPU. And that is more often than not, a *good* thing. After all, if you cant use all of the available resources, then you are wasting money.


Alistair Wall, September 27, 2016 - 2:07 pm UTC

The problem with the original query is that Oracle is evaluating the 'contains' predicate before looking for the nearest neighbour(s), so it does not use the spatial index.

This can be fixed with the hint /*+ index (a(geo_location)) */

spatial queries where clause

Rajeshwaran, Jeyabal, October 09, 2018 - 9:21 am UTC

Team,

We have a table in an application that has longitude and lattitue as NUMBER datatype.
also composite index exists on the columns (LATITUDE, LONGITUDE) in this order.

the sql from the application goes like this.

select *
from the_table
where Latitude BETWEEN 39.188920190130624 AND 42.091677809869374
AND longitude BETWEEN -75.78729387674332 AND -72.017976123256688 ;


this index doesn't help us here, and it goes for Full table scan. so planning to have a spatial index like this.

alter table the_table add x sdo_geometry;

update the_table
set x = sdo_geometry( 2001,null,sdo_point_type( LONGITUDE ,LATITUDE,null),null,null )
where latitude is not null;
commit;

insert into user_sdo_geom_metadata( table_name,column_name,diminfo,srid) 
values( 'THE_TABLE' , 'X', mdsys.sdo_dim_array( mdsys.sdo_dim_element('X',-180,180,0.00005),
     mdsys.sdo_dim_element('Y',-90,90,0.00005)),null);
commit;

create index test_idx 
on the_table( x )     
indextype is mdsys.spatial_index;

exec dbms_stats.gather_table_stats( user,'the_table');


so, could you help us how to rewrite the above sql statement where clause? do i need to go with LOCATOR_WITHIN_DISTANCE or SDO_COVERS operator?
Connor McDonald
October 30, 2018 - 6:23 am UTC

Answered as separate question

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library