Subject: Executive Speakers Announced for Oracle Spatial User ConferenceThe issues of performance are always openly discussed on the PostGIS, JTS etc discussion lists. I have even had honest discussion of performance with the SQL Server Spatial team.
** New Performance Enhancements in Oracle Spatial — Siva Ravada
If I remove the sdo_aggr_set_union to give you some numbers....
select sdo_aggr_set_union(CAST(COLLECT(a.geometry) AS mdsys.SDO_Geometry_Array),0.005) as aggrGeom from largedataset a where a.filter_code = 'WA' group by a.group_code; -- Results ORA-22813: operand value exceeds system limits 22813. 00000 - "operand value exceeds system limits" *Cause: Object or Collection value was too large. The size of the value might have exceeded 30k in a SORT context, or the size might be too big for available memory. *Action: Choose another value and retry the operation.
Unless, of course, I am doing something wrong?
select count(*) as geomCount, round(avg(sdo_util.getNumVertices(a.geometry)),1) as avgVertices from largedataset a where a.filter_code = 'ME' group by a.group_code; -- Results GEOMCOUNT AVGVERTICES --------- ----------- 212 96.2 22 27.1 68 159 191 116.4 374 56.7 58 135.4 275 126.3 65 112.2 110 87 206 143.3 83 173.7 32 142.1 136 186.7 94 151.3 812 136.4 184 119.6 268 112.2 51 137.2 159 173.8 198 160.5 264 118.6 114 103.5 138 93.8 96 86.6 23 188.7 136 199.9 238 134.7 58 142.5 53 142.1 131 179.4 122 96.4 42 133.3 489 162.8 33 rows selected
Since Oracle process memory is shared by many users, it is not a good idea to allow one user to monopolize the memory causing problems to other users.Since when does any software company tell users what is a good idea or not? This sends a bad message to users about their communications with (all) their customers (not just the "big end of town").
This group by won't work as you hit that internal limit in the server.Patently! I provided this example because this is what people want in an aggregate and it is the way most people think of it.
So try the other approach described in the user guide using a function that creates a geometry array and passes it to the union function.OK, two more assertions:
But this is quite inflexible because it really isn't a generic function but rather a function for a specific piece of SQL.
CREATE OR REPLACE FUNCTION Set_Geometry(p_value in varchar2) RETURN SDO_GEOMETRY_ARRAY deterministic AS c_query SYS_REFCURSOR; v_g sdo_geometry; v_GeomArr sdo_geometry_array; BEGIN v_GeomArr := SDO_GEOMETRY_ARRAY(); OPEN c_query FOR 'select a.geometry from admin a where a.blockcode = :1' USING p_value; LOOP FETCH c_query into v_g; EXIT when c_query%NOTFOUND ; v_GeomArr.extend; v_GeomArr(v_GeomArr.count) := v_g; END LOOP; RETURN v_GeomArr; END; / -- Which is called like so: -- select a.group_code, count(*) as aggrCount, sdo_aggr_set_union(set_geometry(a.group_code),0.005) as geoms from admin a where a.hierarchy_code = 'WA' group by a.group_code;
Now I agree SDO_AGGR_SET_UNION is much faster and, with my function, more flexible but it is still ugly.
CREATE OR REPLACE FUNCTION Set_Geometry(p_cursor in SYS_REFCURSOR) RETURN SDO_GEOMETRY_ARRAY DETERMINISTIC AS v_geom sdo_geometry; v_GeomArr sdo_geometry_array; BEGIN v_GeomArr := SDO_GEOMETRY_ARRAY(); LOOP FETCH p_cursor INTO v_geom; EXIT when p_cursor%NOTFOUND ; v_GeomArr.extend; v_GeomArr(v_GeomArr.count) := v_geom; END LOOP; RETURN v_GeomArr; END; / -- Results FUNCTION Set_Geometry compiled -- -- Now here's how to use it in a SELECT .... GROUP BY ... -- select group_code, count(*) as aggrCount, sdo_aggr_set_union(set_geometry(CURSOR(SELECT b.geometry FROM test_table b WHERE b.group_code= a.group_code)),0.005) as geoms from test_table a where a.hierarchy_code = 'WA' group by a.group_code;
I am not an expert on this issue of that system limit of 30k sort area. When we talk to internal groups about it, they alwaysSurely someone in the spatial team is an expert!
ask us to show the customer filed ER. So it will really help if customers like you file an official ER for this.
Having said that, the main problem with this approach is still the multiple copies of the data which tends to take upIn your UC presentation? It is good for the public to know that the OS team is always trying to make things better and faster.
most of the time. So we are coming up with a better way to call sdo_aggr_set_union that avoid this multiple copies of the data.