Top 5 Recent Articles
- Biography (1)
- Blog (41)
- Changes (1)
- Customers (1)
- Data Models (1)
- Education (2)
- General Software (21)
- Georaptor Blog (5)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Blog (4)
- MySQL Spatial (3)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (171)
- Philosophy (1)
- PostGIS (30)
- Press Releases (1)
- Source code (24)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (58)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- Training (1)
- XML (5)
Oracle’s SDO_AGGR_UNION has always had horrible performance where the set of geometry objects to be unioned is somewhat more than trivial.
(Trivial is not a well defined scientific term – experienced users of Oracle Spatial know what I am interfering.)
The Oracle Spatial team were not unaware of the problems with their aggregate function. To be fair on them, the problem lies with the kernel and not their implementation. The problem has to do with the size of the CONTEXT that can be sorted. To get around this, the spatial team’s SDO_AGGR_UNION function errs on the side of completion as against speed of execution.
To get around the problems, the Oracle Spatial team introduced the SDO_AGGR_UNION function.
Yet incorrect use of this function can cause a failure of the SQL as can be seen in the following example:
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.
To be fair on the Oracle Spatial team this is not their preferred method for executing this function (see documentation). In my view, though, it is the most natural given normal SQL use.
Still, the preferred method is to create an “aggregation function” as in the following example:
CREATE OR REPLACE FUNCTION Set_Geometry_by_Value(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 administration a where a.group_code = :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 Set_Geometry_by_Value; / -- 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 administration a WHERE a.hierarchy_code = 'WA' GROUP BY a.group_code; -- Results -- GROUP_CODE AGGRCOUNT GEOMS --------- ---------------------- ------------------ WA 461 SDO_GEOMETRY(.....
But this approach is rather fixed to a single value. One does not want to create set functions for each value of each table….
A better way would be to use a CURSOR as follows:
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; / -- Which is called like so: -- SELECT a.group_code, COUNT(*) AS aggrCount, sdo_aggr_set_union(set_geometry(CURSOR(SELECT b.geometry FROM administration b WHERE b.group_code = a.group_code)),0.005) AS geoms FROM administration a WHERE a.hierarchy_code = 'ME' GROUP BY a.group_code; -- Results -- GROUP_CODE AGGRCOUNT GEOMS --------- ---------------------- ------------------ AS 212 SDO_GEOMETRY(2003,.... ......
But this still is not that natural given the need to repeat the statement for the grouping value.
So, while SDO_AGGR_SET_UNION can be made more flexible it is a solution that really has no future as the only solution is to make SDO_AGGR_UNION perform as quickly as Oracle’s competitors because they all do better than Oracle.
I hope this is of help to someone.