1 2 Previous Next 22 Replies Latest reply: Apr 4, 2013 2:53 PM by B Hall RSS

    SDO_AGGR_MBR

    33450
      Is there a way to make the SDO_AGGR_MBR function make use of the spatial index? It is currently using a full-table scan (ZZZZZZzzzzz) measured in minutes on my small WGS84 dataset.
        • 1. Re: SDO_AGGR_MBR
          33450
          Sorry - forgot the mention it. This is on 10.1.0.4 / win 32.
          • 2. Re: SDO_AGGR_MBR
            91061
            Hi Bryan,

            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.
            • 3. Re: SDO_AGGR_MBR
              33450
              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.
              • 4. Re: SDO_AGGR_MBR
                91061
                Hi Bryan,

                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
                • 5. Re: SDO_AGGR_MBR
                  33450
                  Dan,

                  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.

                  Puzzled...

                  Bryan
                  • 6. Re: SDO_AGGR_MBR
                    91061

                    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

                    Usage Notes

                    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.

                    • 7. Re: SDO_AGGR_MBR
                      33450
                      Dan,

                      The deprecation text was found in the 10g Release 1 (10.1) Part Number B10826-01 manual (downloaded), Chapter 18.
                      • 8. Re: SDO_AGGR_MBR
                        91061
                        thanks Bryan, i don't think it is there anymore
                        • 9. Re: SDO_AGGR_MBR
                          33450
                          Dan...

                          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.

                          PLEASE :-)

                          If not, is there some way for us to just get these values from the indexes, based on a ROWID match or something?
                          • 10. Re: SDO_AGGR_MBR
                            Siva Ravada
                            Bryan,
                            Just to be clear: do you have geodetic data or non-geodetic data ?

                            siva
                            • 11. Re: SDO_AGGR_MBR
                              33450
                              Geodetic (8307), so I can't use the SDO_TUNE.EXTENT_OF shortcut.
                              • 12. Re: SDO_AGGR_MBR
                                Siva Ravada
                                OK, we are working on fixing this for geodetic data.
                                We will have this in 11x.

                                siva
                                • 13. Re: SDO_AGGR_MBR
                                  33450
                                  Thanks!
                                  • 14. Re: SDO_AGGR_MBR
                                    B Hall
                                    Back from the dead... thread. Another company using that four letter software again, same old problem.

                                    Now on 11.2.0.3.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 11.2.0.4 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.

                                    Thoughts?

                                    Bryan
                                    1 2 Previous Next