Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

SDO_AGGR_SET_UNION

Saturday May 12 2012 at 13:34

Keywordsunion polygons sdo_aggr_union sdo_aggr_set_union sdo_geometry examples
Summary

This article presents alternate methods to using Sdo_Aggr_Set_Union introduced in Oracle 11g.

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:

  1. SELECT sdo_aggr_set_union(CAST(COLLECT(a.geometry) AS mdsys.SDO_Geometry_Array),0.005) AS aggrGeom
  2.  FROM largedataset  a
  3.  WHERE a.filter_code = 'WA'
  4.  GROUP BY a.group_code;
  5. -- Results
  6. ORA-22813: operand VALUE exceeds system limits
  7. 22813. 00000 -  "operand value exceeds system limits"
  8. *Cause:    Object OR Collection VALUE was too LARGE. The SIZE OF the VALUE
  9.            might have exceeded 30k IN a SORT context, OR the SIZE might be
  10.            too big FOR available memory.
  11. *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:

  1. CREATE OR REPLACE FUNCTION Set_Geometry_by_Value(p_value IN varchar2)
  2. RETURN SDO_GEOMETRY_ARRAY
  3. deterministic
  4. AS
  5.   c_query   SYS_REFCURSOR;
  6.   v_g       sdo_geometry;
  7.   v_GeomArr sdo_geometry_array;
  8. BEGIN
  9.   v_GeomArr := SDO_GEOMETRY_ARRAY();
  10.   OPEN c_query FOR 'select a.geometry from administration a where a.group_code = :1'
  11.              USING p_value;
  12.    LOOP
  13.     FETCH c_query INTO v_g;
  14.      EXIT WHEN c_query%NOTFOUND ;
  15.      v_GeomArr.extend;
  16.      v_GeomArr(v_GeomArr.COUNT) := v_g;
  17.    END LOOP;
  18.    RETURN v_GeomArr;
  19. END Set_Geometry_by_Value;
  20. /
  21. -- Which is called like so:
  22. --
  23. SELECT a.group_code, COUNT(*) AS aggrCount, sdo_aggr_set_union(set_geometry(a.group_code),0.005) AS geoms
  24.  FROM administration a
  25.  WHERE a.hierarchy_code = 'WA'
  26.  GROUP BY a.group_code;
  27. -- Results
  28. --
  29. GROUP_CODE AGGRCOUNT              GEOMS
  30. --------- ---------------------- ------------------
  31. 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:

  1. CREATE OR REPLACE FUNCTION Set_Geometry(p_cursor IN SYS_REFCURSOR)
  2. RETURN SDO_GEOMETRY_ARRAY
  3. DETERMINISTIC
  4. AS
  5.    v_geom    sdo_geometry;
  6.    v_GeomArr sdo_geometry_array;
  7. BEGIN
  8.    v_GeomArr := SDO_GEOMETRY_ARRAY();
  9.    LOOP
  10.     FETCH p_cursor INTO v_geom;
  11.      EXIT WHEN p_cursor%NOTFOUND ;
  12.      v_GeomArr.extend;
  13.      v_GeomArr(v_GeomArr.COUNT) := v_geom;
  14.    END LOOP;
  15.    RETURN v_GeomArr;
  16. END;
  17. /
  18. -- Which is called like so:
  19. --
  20. SELECT a.group_code,
  21.        COUNT(*) AS aggrCount,
  22.        sdo_aggr_set_union(set_geometry(CURSOR(SELECT b.geometry
  23.                                                 FROM administration b
  24.                                                WHERE b.group_code = a.group_code)),0.005) AS geoms
  25.  FROM administration a
  26.  WHERE a.hierarchy_code = 'ME'
  27.  GROUP BY a.group_code;
  28. -- Results
  29. --
  30. GROUP_CODE AGGRCOUNT              GEOMS
  31. --------- ---------------------- ------------------
  32. AS        212                    SDO_GEOMETRY(2003,....
  33. ......

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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment