Oracle Database Discussions

I have a two Oracle 18c tables:

• ply — Polygons: 4970 rows.
• pnt — Points: 3500 rows.
• The shapes are stored in SDO_GEOMETRY columns.
• Edit: I realize now that most of the points are duplicates (spatially). But I don't think that’s a problem.

The data can be viewed here: db<>fiddle. I can't use Oracle Live SQL because there is a bug with user_sdo_geom_metadata/spatial indexes..

I have a query that selects polygons that intersect at least one point.

  SELECT objectid 
    FROM (SELECT ply.objectid,
                 row_number() over(partition by ply.objectid order by null) rn
            FROM ply                                       --ORDER BY NULL is intentional.
      CROSS JOIN pnt                                       --It doesn't matter what polygon row per OBJECTID gets selected.
           WHERE sdo_anyinteract(ply.shape, pnt.shape) = 'TRUE'
         )
  WHERE rn = 1

   OBJECTID
   ----------
            1
            2
            3
            4
            5
          ...

   1443 rows selected.

--------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
|*  1 |  VIEW                              |           |  1457 | 37882 |       | 12851   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK          |           |  1457 |    10M|    11M| 12851   (1)| 00:00:01 |
|   3 |    NESTED LOOPS                    |           |  1457 |    10M|       | 10522   (1)| 00:00:01 |
|   4 |     TABLE ACCESS FULL              | PNT       |  3500 |    12M|       |    11   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID    | PLY       |     1 |  3848 |       | 10522   (1)| 00:00:01 |
|*  6 |      DOMAIN INDEX (SEL: 0.010000 %)| PLY_SHAPE |       |       |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PLY"."OBJECTID" ORDER BY  NULL )<=1)
   6 - access("MDSYS"."SDO_ANYINTERACT"("PLY"."SHAPE","PNT"."SHAPE")='TRUE')
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The query is designed to only select one row per polygon. Reason: I want a unique list of polygons that intersect at least one point.

The query is relatively slow considering what it does.

• It takes 5 seconds to run in my on-prem Oracle 18c database.
• And it takes 5+ seconds to run in db<>fiddle too.
• I believe the statistics are up-to-date since the tables are newly created in my on-prem database and in db<>fiddle.
• The explain plan indicates that the PLY_SHAPE spatial/domain index is being used, which I think is appropriate.
• I've asked my DBA to enable the SPATIAL_VECTOR_ACCELERATION parameter in the database. I'll re-do my test once it's enabled.
• I don't know if the SPATIAL_VECTOR_ACCELERATION parameter is enabled in db<>fiddle or not. I get an error when I try to query for it: https://topanswers.xyz/fiddle?q=2062.

In comparison, desktop mapping software like ArcGIS Pro can perform the same operation in RAM in 0.5 seconds. Screenshot.

Question:

Can the query performance be improved?

As a novice, it seems surprising to me that my mediocre office computer with mapping software is out-performing our enterprise Oracle database.