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?