My Oracle Support Banner

Spatial Query Using SDO_NN Reading Data From REF CURSOR Takes Too Long (Doc ID 2469097.1)

Last updated on MARCH 09, 2019

Applies to:

Oracle Spatial and Graph - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

The following query runs with poor performance

  SELECT CELLTOWERID,
  ADDRESS,
  CITY,
  STATE,
  ZIP,
  LATITUDE,
  LONGITUDE,
  DISTANCE
 FROM (SELECT DISTINCT
  DS.SITE_INST_ID AS CELLTOWERID,
  CUSTOMER_SITE.ADDRESS,
  CUSTOMER_SITE.CITY,
  CUSTOMER_SITE.STATE_PROV AS STATE,
  CUSTOMER_SITE.POST_CODE_1 AS ZIP,
  DS.LATITUDE,
  DS.LONGITUDE,
  DS.DISTANCE
  FROM
  (SELECT
  SITE_INST_ID, LATITUDE, LONGITUDE, SDO_NN_DISTANCE(1) AS DISTANCE
  FROM <table1>
  WHERE SDO_NN( LOCATION, :1, 'sdo_batch_size = 100 unit = foot', 1) = 'TRUE' ) DS,
  <table2> EPC,
  <table3> CUSTOMER_SITE,
  <table4> MARKET_SITE,
  <table5> CUSTOMER_SITE_SETTINGS
  WHERE EPC.SITE_TYPE = 'CUSTOMER'
  AND EPC.SITE_INST_ID = CUSTOMER_SITE.SITE_INST_ID
  AND CUSTOMER_SITE_SETTINGS.SITE_INST_ID = CUSTOMER_SITE.SITE_INST_ID
  AND CUSTOMER_SITE_SETTINGS.VAL_ATTR_INST_ID = '2445'
  AND CUSTOMER_SITE_SETTINGS.ATTR_VALUE = 'YES'
  AND MARKET_SITE.SITE_INST_ID = CUSTOMER_SITE.PARENT_SITE_INST_ID
  AND DS.SITE_INST_ID = CUSTOMER_SITE.SITE_INST_ID
  ORDER BY DS.DISTANCE
  ) WHERE DISTANCE <= 5000 and ROWNUM <= :2

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


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