我在Oracle 18 c中有一个polygons表和一个points表。

CREATE TABLE polygons (objectid NUMBER(4,0), shape SDO_GEOMETRY);
INSERT INTO polygons  (objectid,shape) 
       VALUES (1,SDO_GEOMETRY(2003, 26917, NULL, sdo_elem_info_array(1, 1003, 1), 
       sdo_ordinate_array(668754.6396, 4869279.7913, 668782.1453, 4869276.1585, 668790.9678, 4869344.6631, 668762.4242, 4869346.22, 668754.6396, 4869279.7913)));

CREATE TABLE points (objectid NUMBER(4,0), shape SDO_GEOMETRY);
INSERT INTO  points (objectid,shape) VALUES (1,SDO_GEOMETRY(2001, 26917, sdo_point_type(668768.133,  4869255.3995, NULL), NULL, NULL));
INSERT INTO  points (objectid,shape) VALUES (2,SDO_GEOMETRY(2001, 26917, sdo_point_type(668770.2088, 4869306.259,  NULL), NULL, NULL));
INSERT INTO  points (objectid,shape) VALUES (3,SDO_GEOMETRY(2001, 26917, sdo_point_type(668817.9545, 4869315.0815, NULL), NULL, NULL));
INSERT INTO  points (objectid,shape) VALUES (4,SDO_GEOMETRY(2001, 26917, sdo_point_type(668782.1134, 4869327.1634, NULL), NULL, NULL));

我有一个查询，它选择在空间上至少相交一个点的多边形。

SELECT poly_objectid
    FROM (SELECT poly.objectid as poly_objectid,
                 row_number() over(partition by poly.objectid order by null) rn
            FROM polygons poly
      CROSS JOIN points pnt
           WHERE sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE'
         )
   WHERE rn = 1

   POLY_OBJECTID 
   ------------- 
               1

-------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |     1 |    26 |    20  (70)| 00:00:01 |
|*  1 |  VIEW                    |          |     1 |    26 |    20  (70)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|          |     1 |  7671 |    20  (70)| 00:00:01 |
|   3 |    NESTED LOOPS          |          |     1 |  7671 |    19  (69)| 00:00:01 |
|   4 |     TABLE ACCESS FULL    | POLYGONS |     1 |  3848 |     3   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL    | POINTS   |     1 |  3823 |    16  (82)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "POLY"."OBJECTID" ORDER BY NULL )<=1)
   5 - filter("MDSYS"."SDO_ANYINTERACT"("POLY"."SHAPE","PNT"."SHAPE")='TRUE')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

db<>fiddle
查询只使用row_number()和WHERE rn = 1为每个多边形选择一行。
问题：
当多边形与多个点相交时，选择哪一行对我来说并不重要。保留任何多边形行都可以。
换句话说，row_number()中的order by null desc是不必要的。
出于简单性和性能方面的原因，有没有办法从analytic/window函数中省略order by null desc？
相关：
Process "first instance only" when using SQL spatial relational functions