Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Idea: Single spatial intersect function that performs as well as SDO_JOIN query

User_1871Apr 4 2023 — edited Apr 5 2023

I have a query that was provided by @kpatenge that performs well: Select polygons that intersect points.

Requirements: Select polygons that intersect at least one point. Only select one row per polygon.

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 
        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'
) 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')

That SDO_JOIN query runs in 0.3 seconds. Whereas other queries I tried were slower: 5 seconds.

Idea:

Could the logic in that high-performing query be bundled up into a single, easy-to-use, OOTB function?

Comments
Post Details
Added on Apr 4 2023
5 comments
94 views