SQL & PL/SQL

Hi everyone,

My SQL is still very basic, hopefully this is not a dumb question ;-)

I have a table with a single spatial column of type SDO_GEOMETRY:

SQL> desc WITHIN_POINT_DISTANCE_TAB
 Name                                            Null?    Type
 ----------------------------------------------- -------- --------------------------------
 POINT                                                    MDSYS.SDO_GEOMETRY

in which I store only single point geometries. I discovered that using the min/max SQL operators to get the combined extend of my points is faster than using SDO_TUNE.EXTEND_OF:

SQL> select min(e.point.sdo_point.x) min_x, max(e.point.sdo_point.x) max_x, min(e.point.sdo_point.y) min_y, max(e.point.
sdo_point.y) max_y from WITHIN_POINT_DISTANCE_TAB e;

     MIN_X      MAX_X      MIN_Y      MAX_Y
---------- ---------- ---------- ----------
-44.700001 737.400024 -23.870001 1094.83008

Elapsed: 00:00:00.01

but it bothers me a bit to repeat e.point.sdo_point 4 times in the above (what can I say, I'm a developer ;-) So I thought I could use a nested query to select all the SDO_POINT_TYPE sdo_point's and refer it using a p alias, and be able to simplify the query to:

SQL> select min(p.x), min(p.y), max(p.x), max(p.y) from (select t.point.sdo_point from WITHIN_POINT_DISTANCE_TAB t) p;
select min(p.x), min(p.y), max(p.x), max(p.y) from (select t.point.sdo_point from WITHIN_POINT_DISTANCE_TAB t) p
                                         *
ERROR at line 1:
ORA-00904: "P"."Y": invalid identifier

But obviously this is incorrect, yet I'd like to understand what I'm missing here.

I posited that maybe the sub-query can't return an object member and needs to return a column, but that doesn't appear to be the case:

SQL> select min(p.sdo_point.x), min(p.sdo_point.y), max(p.sdo_point.x), max(p.sdo_point.y) from (select t.point from WIT
HIN_POINT_DISTANCE_TAB t) p;
select min(p.sdo_point.x), min(p.sdo_point.y), max(p.sdo_point.x), max(p.sdo_point.y) from (select t.point from WITHIN_P
OINT_DISTANCE_TAB t) p
                                                                       *
ERROR at line 1:
ORA-00904: "P"."SDO_POINT"."Y": invalid identifier

Can someone please explain why the nested query approach fails as written above?

More generally, is it a bad idea to go for a nested query just for syntactic reasons? Are there performance implications going the nested query route above? (I actually wanted to see the perf. implications experimentally, but since it fails I can't...)

Any insight would be appreciated. Thanks, --DD