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.