1

I experienced bad performance when using the sdo_aggr_mbr function on a data set of more than 1 million rows, I had to terminate the operation after several minutes.

The statement was: 

select sdo_aggr_mbr(MY_GEOMETRY) from MY_TABLE

When I execute a similar operation, to create the bounding box in a manual way, the performance is very good:

select min(t.MY_GEOMETRY.sdo_point.x), 
max( t.MY_GEOMETRY.sdo_point.x), 
min(t.MY_GEOMETRY.sdo_point.x),
max(t.MY_GEOMETRY.sdo_point.y)  
from MY_TABLE t;

That indicates that the database and the spatial index of the geometry column is configured in a correct way.

Why is the aggregation function of oracle is so slow and what can be done to improve the performance?

Improve this question
2
  • 1
    As far as I know that is a feature of Oracle Spatial in all versions up till 11g which is the latest I have been using. Write a question or a bug report to Oracle and see if it helps. However, it does not affect all the spatial functions and sdo_aggr_mbr in not so often really needed. Unfortunately many GIS programs use it for getting the extents of the layer probably because it is a fast query in PostGIS. Commented Jul 3, 2014 at 13:01
  • Could the optimizer cut some corners on this query? Yes. But this is a full table scan query which does not use indexing. Assuming that all geometry operations are slow because of one unbounded query isn't wise. Commented Jul 3, 2014 at 13:26

2 Answers 2

Reset to default
5

Based on the SQL present in your query, you may want to change the spatial function you're using. In the past, the SDO_TUNE.EXTENT_OF('TABLE_NAME', 'SPATIAL_COLUMN') has given us better performance and, according to the below link (assuming the documentation has been kept up-to-date), that is the proper way to get the MBR for a table in the specified scenarios (although I am unsure if they apply to you).

The SDO_AGGR_MBR function, documented in Chapter 20, also returns the MBR of geometries. The SDO_TUNE.EXTENT_OF function has better performance than the SDO_AGGR_MBR function if the data is non-geodetic and if a spatial index is defined on the geometry column; however, the SDO_TUNE.EXTENT_OF function is limited to two-dimensional geometries, whereas the SDO_AGGR_MBR function is not. In addition, the SDO_TUNE.EXTENT_OF function computes the extent for all geometries in a table; by contrast, the SDO_AGGR_MBR function can operate on subsets of rows.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28400/sdo_objtune.htm#SPATL1216

If you need to use the SDO_AGGR_MBR, dividing the aggregation into discrete pieces may give you a performance increase. We've found that when doing aggregations, one of the easiest ways to improve performance is to do the aggregation on multiple smaller datasets, then aggregate the resulting datasets (divide the single operation into multiple operations). A reduction in the overall complexity of the geometry allows for increased performance.

We've used this to improve SDO_AGGR_UNION performance in the past (we've since stopped using it due to a reduction in the number of polygons we need to aggregate, so I cannot vouch for performance on any recent versions) and I imagine that it may have a similar impact in the performance of SDO_AGGR_MBR. Instead of using a single query, create subqueries that work based on some criteria.

SELECT SDO_AGGR_MBR(MY_GEOM) MY_GEOM
FROM
(
  SELECT SDO_AGGR_MBR(MY_GEOM) MY_GEOM
  FROM
  (
    SELECT SDO_AGGR_MBR(MY_GEOM) MY_GEOM
    FROM
    (
      SELECT SDO_AGGR_MBR(MY_GEOM) MY_GEOM
      FROM MY_TABLE
      GROUP BY MOD(ROWNUM, 1000)
    )
    GROUP BY MOD (ROWNUM, 100)
  )
  GROUP BY MOD (ROWNUM, 10)
);
Improve this answer
0

The spatial aggregate operations tend to be expensive, especially those that go above and beyond the simple MBR calculation (aggregate union, aggregate convex hull, aggregate. They have been massively improved in 12c through the Vector Performance Acceleration mechanism of Oracle Spatial.

The MBR aggregation is simpler, but still needs to do a lot of work, since the input shapes may contain arcs, and so simply getting the min and max of the X's and Y's is not sufficient.

If your purpose is to get the bounding box of an entire layer, the best is indeed as said to use the SDO_TUNE.EXTENT() function. It will extract the bounding box directly from the root node of the spatial index and so will be very fast.

Improve this answer
3
  • Unfortunately, even though I did not express this in my question, I need to calculate the MBR for a subset too, so the SDO_TUNE.EXTENT() function is no option. I hoped that there is a way to tune the MBR function, maybe by informing the operation that it just handles a 2D point, but it seems that there is nothing what can be done. Commented Jul 4, 2014 at 12:42
  • If your need is only for points, then the approach you described (selecting the min and max of x and y values of the points) is the most effective. If it gives you the performance you expect, you can just run with that. Commented Jul 4, 2014 at 18:51
  • Making the result into an actual geometry object is trivial: just use the values you get to feed an SDO_GEOMETRY() rectangle constructor. Commented Jul 4, 2014 at 18:52

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.