Efficient way to find geometries that intersect a given rectangle

Question

It feels I'm missing something absolutely basic here OR there is something weird in the internals of Oracle's spatial indexing (I sincerely hope it is just me missing something basic or I am about to lose some faith in Oracle).

I'm trying to find which geometries intersect a given rectangle:

I have a rectangle, let's call it VIEWPORT with Latitude/Longitude 41.500, 12.500 and 42.000, 13.000

on-the-fly that is SDO_GEOMETRY(2003,4326,null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000))

Also I have in a table AREAS a bunch of geometries (polygons and multipolygons). Spatial indexed.

Now, I want to see which records from AREAS intersect that VIEWPORT. So, in my SELECT I should have a WHERE clause like this:

SDO_RELATE
(
    SDO_GEOMETRY(2003, 4326, null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000)),
    "AREAS"."GEOMETRY",
    'mask=ANYINTERACT'
)='TRUE'

Well this can't execute because ORA-13226: interface not supported without a spatial index. That happens because the first parameter in SDO_RELATE is the on-the-fly SDO_GEOMETRY, which has no spatial index.

So let's flip the order of the arguments (which is not correct approach but anyway) just to mesaure the execution time. So the where clause goes like this:

SDO_RELATE
(
    "AREAS"."GEOMETRY",
    SDO_GEOMETRY(2003, 4326, null,SDO_ELEM_INFO_ARRAY(1,1003,3),mdsys.SDO_ORDINATE_ARRAY(12.500, 41.500, 13.000, 42.000)),
    'mask=ANYINTERACT'
)='TRUE'

"AREAS"."GEOMETRY" is spatial indexed, so no problem for SDO_RELATE to execute. I'm getting the correct results in 8.8 seconds. But, that's not efficient at all.

Now let's do something tricky. Instead of providing that VIEWPORT rectangle as a on-the-fly constructed SDO_GEOMETRY let's put it in a table, with spatial index. This way we can use it as the first argument on SDO_RELATE. So eventually we have:

WITH viewport AS
(
    SELECT * FROM "VIEWPORT_TEMP_TABLE" WHERE "ID" = 1
)
.
.
.
SDO_RELATE
(
    viewport."GEOMETRY",
    "AREAS"."GEOMETRY",
    'mask=ANYINTERACT'
)='TRUE'

This executes just in 3.8 sec, the difference is unexpectedly great!

So Oracle has the potential to perform this in 3.8 sec but only with this hacky way.

I think in this point, either I am ignoring something very basic and mis-using or mis-judging Oracle, or something is very "weird" in how Oracle performs intersections with a given SDO_GEOMETRY.

My question is: What is the correct/efficient way to find the geometries that intersect a given rectangle in Oracle?

As I demonstrated the fastest way is to create a temporary table, insert that given rectangle and rebuild its spatial index and then use it as first argument in the SDO_RELATE. This is way too hacky to be the correct way to perform something so simple like this.

So, what is the correct way to find geometries intersecting a given rectangle?

(In contrast, postgres has no problem with on-the-fly given rectangle in the very same scenario and that made me very sceptical about Oracle)

edit: running on

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Answer 1

The problem is Option A, you're missing something very basic. (I do it all the time, so I'm not throwing stones.) I believe you are misinterpreting the Oracle documentation on SDO_RELATE() found here. Single geometries (described as "transient geometries" in the documentation) are SUPPOSED to be the second parameter, while the table-full of geometries are supposed to be the first argument.

Not to put too fine a point on it, but even in the suggested SDO_ANYINTERACT() function, the documentation shows your singleton geometry should be the second parameter:

Two more supportive points:
Perhaps a not-so-temporary table of transient 'Viewport' geometries isn't such a bad design. You could keep it around, indexing it, inserting requested viewports then referencing each request row from it (by PK) in a WITH clause?

Lastly, your 11.2.0.1 release needs some SDO patching; I suggest well-placed comments to your DBAs on reving/patching up to at least 11.2.0.4