Query Optimization: Select polygons that intersect points

Question

I have 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 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.

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.

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.

A desktop application may use graphic card hardware acceleration to solve the problem, so you may compare apples and oranges. — p3consulting, Apr 02 '23 at 11:08
@p3consulting Interesting. I just have a 5-year old Lenovo M700 with integrated graphics: https://www.amazon.ca/THINKCENTRE-M700-SFF-Business-Bit-Multi/dp/B07VZVMFZW/ref=sr_1_10?crid=O8XWGHZSQKB7. Do you have any idea if graphic card hardware acceleration can be done with integrated graphics? Or does it only apply to dedicated graphics cards? — User1974, Apr 03 '23 at 04:20

score 2 · Answer 1 · answered Apr 03 '23 at 14:53

2

You can start using SDO_JOIN in conjunction with SDO_ANYINTERACT and go forward from there.

SELECT /*+ ordered use_nl (a,b) use_nl (a,c) */ 
    b.objectid as point_object_id, 
    c.objectid as polygon_object_id
FROM 
    TABLE(sdo_join('PNT','SHAPE','PLY','SHAPE')) a,
    PNT b,
    PLY c
WHERE 
    a.rowid1 = b.rowid
AND a.rowid2 = c.rowid
AND SDO_GEOM.RELATE (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE';

answered Apr 03 '23 at 14:53

kpatenge

71
2

I wonder why `sdo_geom.relate (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE'` (0.3 seconds) performs better than `sdo_relate(b.shape, c.shape, 'mask=anyinteract') = 'TRUE'` (1.4 seconds). The docs seem to suggest operators like `sdo_relate` perform better than functions like `sdo_geom.relate`. Yet your query and my testing would suggest otherwise. – User1974 Apr 06 '23 at 05:10
[1.9 Spatial Operators, Procedures, and Functions](https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-concepts.html#GUID-2CFC33C7-D5A6-4360-B9EB-9A147188C244) — *"If an operator and a procedure or function perform comparable operations, and if the operator satisfies your requirements, use the operator. For example, unless you need to do otherwise, use SDO_RELATE instead of SDO_GEOM.RELATE..."* – User1974 Apr 06 '23 at 05:12

User1974 · Answer 2 · 2023-04-13T20:36:20.893

I took @kpatenge's excellent answer and added the last small step: only select one row per polygon, using row_number() and WHERE rn = 1.

It runs instantly: 0.3 seconds.

select objectid from (
    select /*+ ordered use_nl (a,b) use_nl (a,c) */    --Try removing this hint. Removing it seemed to reduce the cost without impacting performance.
        c.objectid as objectid,
        row_number() over(partition by c.objectid order by null) rn
    from 
        sdo_join('PNT','SHAPE','PLY','SHAPE') a,       --I removed table(), since that's no longer necessary. https://stackoverflow.com/q/72590279/5576771
        pnt b,
        ply c
    where 
        a.rowid1 = b.rowid
        and a.rowid2 = c.rowid
        and sdo_geom.relate (b.shape, 'ANYINTERACT', c.shape, 1.0) = 'TRUE'   --1.0 is the tolerance: https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-concepts.html#GUID-7469388B-6D23-4294-904F-78CA3B7191D3
) where rn = 1;

----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |   142K|  3607K|       |   297K  (1)| 00:00:12 |
|*  1 |  VIEW                                 |          |   142K|  3607K|       |   297K  (1)| 00:00:12 |
|*  2 |   WINDOW SORT PUSHED RANK             |          |   142K|    13M|    17M|   297K  (1)| 00:00:12 |
|   3 |    NESTED LOOPS                       |          |   142K|    13M|       |   294K  (1)| 00:00:12 |
|   4 |     NESTED LOOPS                      |          |   285K|    14M|       |  8204   (1)| 00:00:01 |
|   5 |      COLLECTION ITERATOR PICKLER FETCH| SDO_JOIN |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY USER ROWID       | PNT      |    35 |  1820 |       |     1   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS BY USER ROWID        | PLY      |     1 |    46 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "C"."OBJECTID" ORDER BY  NULL )<=1)
   6 - access(CHARTOROWID(VALUE(KOKBF$)))
   7 - access(CHARTOROWID(VALUE(KOKBF$)))
PLAN_TABLE_OUTPUT                                                                                                                                --------------------------------------------------------
       filter("SDO_GEOM"."RELATE"("B"."SHAPE",'ANYINTERACT',"C"."SHAPE",1.0)='TRUE')

Query works in on-prem Oracle database, but not in online environment: ORA-13236 (SDO_Join)

Edit: (a simplified query suggested by @AlbertGodfrind)

Time: 0.2 - 0.3 seconds.

    select distinct
        c.objectid as objectid
    from 
        sdo_join('PNT','SHAPE','PLY','SHAPE','MASK=anyinteract') a,
        pnt b,
        ply c
    where 
        a.rowid1 = b.rowid
        and a.rowid2 = c.rowid

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                      |  4970 |   145K|  1872  (99)| 00:00:01 |
|   1 |  HASH UNIQUE                         |                      |  4970 |   145K|  1872  (99)| 00:00:01 |
|*  2 |   HASH JOIN                          |                      |    14M|   406M|   197  (82)| 00:00:01 |
|   3 |    INDEX FAST FULL SCAN              | R202801_SDE_ROWID_UK |  4970 | 79520 |     4   (0)| 00:00:01 |
|*  4 |    HASH JOIN                         |                      |   285K|  3908K|    37  (11)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN             | R202802_SDE_ROWID_UK |  3500 | 42000 |     4   (0)| 00:00:01 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH| SDO_JOIN             |  8168 | 16336 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C".ROWID=CHARTOROWID(VALUE(KOKBF$)))
   4 - access("B".ROWID=CHARTOROWID(VALUE(KOKBF$)))

I suggest two simplifications here: [1] remove the partitioning and just do a `select distinct c.objectid` that has the same effect (remove duplicate matches), and [2] remove the`sdo_geom.relate()` filter. Just add that inside the `sdo_join` operator: `sdo_join('PNT','SHAPE','PLY','SHAPE','MASK=anyinteract')` — Albert Godfrind, Apr 11 '23 at 11:01
@AlbertGodfrind Thanks Albert. I added a simplified query to my answer, as you suggested. — User1974, Apr 11 '23 at 13:39

Query Optimization: Select polygons that intersect points

2 Answers2

Linked