Query using SDO_NN Return 0 Rows - Usage of SDO_BATCH_SIZE versus SDO_NUM_RES (Doc ID 3056987.1)

Last updated on NOVEMBER 14, 2024

Applies to:

Symptoms

Query using SDO_NN Return 0 Rows.

Test Case:

create user USER_EXAMPLE identified by test default tablespace users temporary tablespace temp;

grant connect,create session,dba to USER_EXAMPLE;

connect USER_EXAMPLE/test

drop table TABLE1_EXAMPLE;

drop table TABLE1_EXAMPLE_V2;

drop table TABLE2_EXAMPLE;

delete USER_SDO_GEOM_METADATA where table_name in ('TABLE1_EXAMPLE','TABLE1_EXAMPLE_V2','TABLE2_EXAMPLE');

commit;

CREATE TABLE "TABLE1_EXAMPLE"

("COMPID" NUMBER(10,0),

"COMTYP" CHAR(2 BYTE),

"GEOMETRY" "MDSYS"."SDO_GEOMETRY");

CREATE TABLE "TABLE2_EXAMPLE"

("OBJ_1" NUMBER(10,0),

"COMTYP_1" VARCHAR2(2 BYTE),

"SPNIV_1" NUMBER(10,0),

"GENERIC_1" VARCHAR2(41 BYTE),

"NETAREA" VARCHAR2(10 BYTE),

"GENERIC_2" VARCHAR2(10 BYTE),

"GENERIC_3" NUMBER(10,2),

"GENERIC_3_TYPE" VARCHAR2(32 BYTE),

"GENERIC_PKT_X" NUMBER(10,3),

"GENERIC_PKT_Y" NUMBER(10,3),

"GEOMETRY" "MDSYS"."SDO_GEOMETRY" ,

"ID" NUMBER(10,0),

"CHECK_1" NUMBER(1,0),

"BEARING" NUMBER(10,3),

"DIFFBEARING" NUMBER(10,3));

SET DEFINE OFF;

Insert into TABLE1_EXAMPLE (COMPID,COMTYP,GEOMETRY) values (-61019059,'ST',MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(455173.201085,6463291.84623,NULL),NULL,NULL));

Insert into TABLE1_EXAMPLE (COMPID,COMTYP,GEOMETRY) values (-61018708,'ST',MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(455170.118229,6463296.08872,NULL),NULL,NULL));

Insert into TABLE2_EXAMPLE (OBJ_1,COMTYP_1,SPNIV_1,GENERIC_1,NETAREA,GENERIC_2,GENERIC_3,GENERIC_3_TYPE,GENERIC_PKT_X,GENERIC_PKT_Y,GEOMETRY,ID,CHECK_1,BEARING,DIFFBEARING)

values (72019360,'LL',45000,'TL171','VFV',null,7,'brytpunkt',455205.8,6463305.72,MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(455205.8,6463305.72,NULL),NULL,NULL),44431,1,74.305,-0.008);

Insert into USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) values ('TABLE1_EXAMPLE','GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',130000,1020000,0.005),MDSYS.SDO_DIM_ELEMENT('Y',6100000,7680000,0.005)),null);

Insert into USER_SDO_GEOM_METADATA (TABLE_NAME,COLUMN_NAME,DIMINFO,SRID) values ('TABLE2_EXAMPLE','GEOMETRY',MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',130000,1020000,0.005),MDSYS.SDO_DIM_ELEMENT('Y',6100000,7680000,0.005)),null);

commit;

create index OBJ_1_idx on TABLE2_EXAMPLE (OBJ_1);

create index geometry_sdx on TABLE2_EXAMPLE (geometry) indextype is mdsys.spatial_index_v2;

create index TABLE1_EXAMPLE_idx on TABLE1_EXAMPLE (compid);

create index TABLE1_EXAMPLE_sdx on TABLE1_EXAMPLE (geometry) indextype is mdsys.spatial_index_v2;

Select

t1.OBJ_1, t1.GENERIC_3, t1.GENERIC_3_TYPE, NULL,

t3.compid objid,t3.COMTYP, round(t3.geometry.sdo_point.x,2) x,

round(t3.geometry.sdo_point.y,2) y,

round(sdo_geom.sdo_distance(t3.geometry,t1.geometry),2) avstand, 500 radie,

NULL,NULL, t3.geometry geometry

from TABLE1_EXAMPLE t3, TABLE2_EXAMPLE t1

where t3.compid <> -61019059

and sdo_nn(t3.geometry,t1.geometry,'sdo_num_res=1 distance =500') = 'TRUE';

no rows selected

--If we modify the sql, changing sdo_num_res=2 and add FIRST ROW ONLY we get what wanted, 1 row back.

Select

t1.OBJ_1, t1.GENERIC_3, t1.GENERIC_3_TYPE, NULL,

t3.compid objid,t3.COMTYP, round(t3.geometry.sdo_point.x,2) x,

round(t3.geometry.sdo_point.y,2) y,

round(sdo_geom.sdo_distance(t3.geometry,t1.geometry),2) avstand, 500 radie,

NULL,NULL, t3.geometry geometry

from TABLE1_EXAMPLE t3, TABLE2_EXAMPLE t1

where t3.compid <> -61019059

and sdo_nn(t3.geometry,t1.geometry,'sdo_num_res=2 distance =500') = 'TRUE'

order by avstand

fetch FIRST ROW ONLY;

OBJ_1 GENERIC_3 GENERIC_3_TYPE N OBJID CO X Y AVSTAND RADIE N N GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

72019360 7 brytpunkt -61018708 ST 455170.12 6463296.09 36.96 500 SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(455170.118, 6463296.09, NULL), NULL, NULL)

Changes

 N/A

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.