Top 5 Recent Articles

ARTICLES CATEGORIES

SDO_AGGR_SET_UNION

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.