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>