SDO_NN query using SDO_BATCH_SIZE performs poorly (Doc ID 1331053.1)

Last updated on SEPTEMBER 01, 2011

Applies to:

Oracle Spatial - Version: 10.2.0.3 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

SDO_NN query using SDO_BATCH_SIZE performs poorly while an SDO_NN query using SDO_NUM_RES running from the same machine and with the same dataset works very fast.

SQL> -- I want the nearest thirty points to the search set
SQL> -- points (5 points). Using sdo_batch_size.
SQL>
SQL> -- this query runs slow
SQL> SELECT /*+ ORDERED */ b.cod_polo, a.cod_polo
  2   FROM (SELECT * FROM STORES WHERE ROWNUM <= 5) B, STORES A
  3   WHERE A.cod_polo IN (SELECT p.cod_polo
  4                        FROM STORES p
  5                        WHERE SDO_NN(p.geom, b.geom, 'sdo_batch_size=30') = 'TRUE'
  6                          AND B.COD_POLO <> P.COD_POLO
  7                          AND ROWNUM <= 30);

  COD_POLO   COD_POLO
---------- ----------
       202        203
       202        204
...
       206        231
       206        232

150 rows selected.

Elapsed: 00:04:40.80


SQL> -- I want the nearest thirty points to the search set
SQL> -- points (5 points). Using sdo_num_res.
SQL>
SQL> -- this query runs fast.
SQL> SELECT b.cod_polo, a.cod_polo
  2  FROM (SELECT * FROM STORES WHERE ROWNUM <= 5) B, STORES A
  3        WHERE A.cod_polo IN (SELECT /*+ ORDERED */ p.cod_polo
  4        FROM STORES p
  5        WHERE SDO_NN(p.geom, b.geom, 'sdo_num_res=30') = 'TRUE');

  COD_POLO   COD_POLO
---------- ----------
       202        202
       202        300
...
       206        994
       206        995

150 rows selected.

Elapsed: 00:00:00.11

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms