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:
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.