Oracle 18c中能否省略ROW_NUMBER()窗口函数的ORDER BY子句？

表结构与初始化数据 #

我在Oracle 18c环境下有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));

当前查询逻辑 #

我需要筛选出与至少一个点空间相交的多边形，为了确保每个多边形只返回一行，我用ROW_NUMBER()窗口函数配合ORDER BY 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

当前执行计划 #

该查询的执行计划如下：

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

问题 #

因为当一个多边形和多个点相交时，我不关心返回哪一行，所以ORDER BY NULL并非必需。请问能不能从窗口函数里省略ORDER BY NULL，简化语句同时提升查询性能？

回答 #

1. 能否省略ORDER BY NULL？
   可以直接省略。Oracle的ROW_NUMBER()窗口函数在不指定ORDER BY子句时，会默认采用无序的方式分配行号，这完全符合你的需求——只要每个多边形保留任意一行相交记录即可。

2. 对性能的影响
   从执行计划来看，当前查询的WINDOW SORT PUSHED RANK操作占了主要CPU开销（70%）。当省略ORDER BY NULL后，Oracle不需要为分区内的数据执行排序操作，理论上会减少排序带来的CPU消耗，降低执行成本。不过实际性能提升幅度还取决于数据量大小：

   • 小数据量下差异可能不明显；
   • 当多边形和点的数量很大时，省去排序步骤能显著缩短执行时间。

3. 更优的替代方案
   其实你当前的写法有点绕，完全可以用更简洁高效的方式实现需求：

   • 用DISTINCT直接去重：

     SELECT DISTINCT poly.objectid as poly_objectid
     FROM polygons poly
     JOIN points pnt ON sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE'
     

   • 或者用EXISTS子查询（性能通常更优，因为找到第一个相交点就会停止扫描）：

     SELECT poly.objectid as poly_objectid
     FROM polygons poly
     WHERE EXISTS (
         SELECT 1 FROM points pnt
         WHERE sdo_anyinteract(poly.shape, pnt.shape) = 'TRUE'
     )
     

   这两种写法都比窗口函数的方式更简洁，且EXISTS的执行逻辑更高效，尤其当单多边形匹配多个点时，不需要扫描所有匹配点，找到第一个满足条件的就会终止。

内容的提问来源于stack exchange，提问作者User1974