Use ROW_NUMBER() window function without specifying ORDER BY

Question

I have a polygons table and a points table in Oracle 18c.

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

And I have a query that selects polygons that spatially intersect at least one point.

  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

The query only selects one row per polygon using row_number() and WHERE rn = 1. That works as expected.

---

Question:

It doesn't matter to me what row is selected when a polygon intersects multiple points. Keeping any polygon row is fine.

In other words, order by null desc in row_number() is unnecessary.

Is there a way to omit order by null desc from the analytic/window function, for simplicity and performance reasons?

---

Related:

• Process "first instance only" when using SQL spatial relational functions
• Select polygons that intersect points (improve performance)

Answer 1

Is there a way to omit order by null desc from the analytic/window function, for simplicity and performance reasons?

No,the ROW_NUMBER analytic function documentation gives the syntax as:

ROW_NUMBER

Syntax

The ORDER BY clause is not optional.

Answer 2

You may simply use rownum if you do not need an order by some specific column(s). That rownum simply is the order in which Oracle finds the result rows, without any spcific rule.