This content has been marked as final. Show 22 replies
Sorry - forgot the mention it. This is on 10.1.0.4 / win 32.
Unfortunately not. SDO_AGGR_MBR is a set function, and spatial doesn't know which geometries will be passed into it.
There is a spatial procedure SDO_TUNE.EXTENT_OF that can use the index of projected data (it always calculates the extent of a complete spatial layer). Unfortunately it can't be set up to work with a geodetic layer.
There will be some performance benefit to using sdo_tune.extent_of. On some "ballpark" tests I've run it executed 3 to 4 times faster.
Well, thanks for the answer, that's what I figured.
Could a version that uses the indexes please be considered for a future version? Using the pre-computed index MBR's should be MUCH faster (several orders of magnitude). Two coordinates per item sure beat 30 to 100 coordinates per polygon, plus reading past all the other columns of data.
For now, I'll have to pre-compute the MBR and refresh it at some interval in another table.
Just to make sure you know, the enhancement request will be:
update sdo_tune.extent_of to use the index mbr with geodetic data the layer is indexed.
sdo_aggr_mbr will never be updated in this manner
I'm really confused now. The 10g OS manual states that the sdo_tune.extend_of function is deprecated, and that we are encouraged to use the SDO_AGGR_MBR function instead.
There had been plans to deprecate this feature, until the implementation of using the index to get the MBR.
I hope it doesn't still say deprecated anywhere in the 10gR2 manual. Where is it in the 10g R1 manual?
This is the 10g R2 usage notes for SDO_TUNE.EXTENT_OF
The SDO_AGGR_MBR function, documented in Chapter 12, 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.
The deprecation text was found in the 10g Release 1 (10.1) Part Number B10826-01 manual (downloaded), Chapter 18.
thanks Bryan, i don't think it is there anymore
Any chance that in the last year someone has looked at this issue again? 11xi?
I would REALLY like to have an internal function that can use the index MBR's to find the MBR of a number of records in a table.
Since SDO_AGGR_MBR is just too slow for this purpose on anything more than a few thousand rows, I have resorted to storing the lower-left and upper-right x/y values in each row myself, updated with triggers. Simple MIN/MAX calls find the boundary's VERY quickly.
However, again, since that data is already in the spatial index, it would sure be nice to just be able to make use of it instead of my cobbled together solution. Common things, like table metadata (or mine - by location) really can use this.
The non-spatial filter (row elimination) DOES help a lot, but a recient test of 55K returned records still takes over three minutes using this. The same querry, using MIN/MAX on my indexed x/y's takes less than a second.
If not, is there some way for us to just get these values from the indexes, based on a ROWID match or something?
Just to be clear: do you have geodetic data or non-geodetic data ?
Geodetic (8307), so I can't use the SDO_TUNE.EXTENT_OF shortcut.
OK, we are working on fixing this for geodetic data.
We will have this in 11x.
Back from the dead... thread. Another company using that four letter software again, same old problem.
Now on 22.214.171.124.5 on AIX 6.1 with SRID 4269 - but it seems the issue still exists with geodetic data (I guess that improvement was never implemented). Any hope of an indexed version in 126.96.36.199 or 12c? Or is this a "new" bug in the current version?
18 minutes to run this in serial, or 5-1/2 minutes if I let it loose with default parallelism with 5 cores. Table is spatially indexed and analyzed, has only 125K rows (all 2D lines) in 39 MB.