sqloracle-databasewindow-functionsoracle18coracle-spatial

Use ROW_NUMBER() window function without specifying ORDER BY

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

enter image description here

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:

Solution

  • 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

    ROW_NUMBER Syntax diagram

    The ORDER BY clause is not optional.