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?

The function specification is clear: `ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)`. It is not optional. — astentx, Mar 28 '23 at 04:34
This is SQL. You're not telling the system *what to do*, you're telling it *what result you want*. You say you don't care about which row is selected but do you care about whether you will obtain the same results if you run the query a second time? If so, you need to write something *deterministic*, which includes an `ORDER BY` specification. Without actually measuring the plans the optimizer produces, you have no idea how expensive any sort operation (if actually required) might be. — Damien_The_Unbeliever, Mar 28 '23 at 05:34

score 1 · Accepted Answer · answered Mar 28 '23 at 08:17

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.

answered Mar 28 '23 at 08:17

MT0

143,790
11
59
117

score 0 · Answer 2 · answered Mar 28 '23 at 07:34

0

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.

answered Mar 28 '23 at 07:34

Bogdan Dincescu

683
6

Use ROW_NUMBER() window function without specifying ORDER BY

2 Answers2

`ROW_NUMBER`

Syntax