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

Last updated on APRIL 17, 2023

Applies to:

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

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