1 2 Previous Next 23 Replies Latest reply: Dec 2, 2013 6:34 PM by davidp 2 Go to original post RSS
      • 15. Re: to upgrade = SLOW sdo_geometry return
        Ying Hu-Oracle
        Hi Bryan,

        Please note that Bug 11797492, which you mentioned, is not a spatial bug.
        If you'd like to discuss your spatial index problems, please make them specific.


        Edited by: yhu on Jul 27, 2011 6:19 AM
        • 16. Re: to upgrade = SLOW sdo_geometry return

          Thank you for looking at the spatial forum, us users really appreciate this. But - really? How was my SR 3-2097288191 not specific? Please explain.

          In a nutshell: SDO_FILTER is broken in 11gR2. It makes spatial selections slow as molasses in winter. Oracle's response is that it is NOT a bug, and was put in as a feature enhancement for the elusive 12 version.

          Out of the blue a few weeks later Oracle contacted me by phone (I guess so there is no electronic record) and told me that they realized it was a bug, and a patch for the 11 series would be forthcoming.

          It didn't. I and the others here are still waiting.

          As far as we are concerned, unless you accept the (user provided) workaround that fixes (and as reported here by a user does no longer work with - 11gR2 is completely unusable for vector spatial operations. SDO_FILTER is a CORE function for spatial - not some fringe item.

          So at this moment, the only option is to stay with the still functional 10gR2 release. If some security patch that we are supposed to apply breaks it, we will simply apply for a waiver to keep from pushing it to production.

          If / when Oracle moves this to a #1 concern and actually put out a patch to correct this defect we will not move to 11gR2. And since 10gR2 works fine for us, we simply see no reason to pay for support for spatial that does not seem to exist.

          Who knows, at this rate, 12 may be out before anything changes. And maybe 12 will correct the issue, and maybe not. All we have is Oracle's word on this at the moment.

          Spatial Architect
          • 17. Re: to upgrade = SLOW sdo_geometry return
            Ying Hu-Oracle

            You mentioned that the following workaround worked for you on


            If so, it will also work on . Have you tried Anyway, if you
            find any problems, please let us know.

            Bug 11797492 is related to query transformation, i.e. a view (not a table)
            is used in the from clause. Note that this is not a spatial bug, as far as I know.

            • 18. Re: to upgrade = SLOW sdo_geometry return
              Good news... I have several USAF Oracle reps pushing for a resolution to this now. Something may actually happen.

              I think they are finally understanding that this IS a serious bug and should be fixed in 11g. It is currently marked as "Development Working". I'll keep you all advised if/when a proper patch is created. We may yet be able to use 11g in production before 12 comes out.

              As to the issue, the "not recommended but we have no other solution" work-around does still work on an upgraded version. However - that index work-around does not avoid the recursive SELECT DIMINFO call that still slows down spatial selects.

              • 19. Re: to upgrade = SLOW sdo_geometry return
                Update... things are looking up:

                A request has been submitted on Bryan's behalf to have the fix for Bug 9743250 "FULL TABLE SCANS IN SPATIAL QUERIES" included in when it becomes available, which should be before June 2013.

                • 20. Re: to upgrade = SLOW sdo_geometry return
                  B Hall

                  In case you didn't notice, the bug-fix for this was included in the patchset, and the 12.x release. So no more index-only work-around!


                  Today I will get to finally see the results on one of our dev databases, as the AIX version (what I'm now running) of was just released this week.


                  Although the documentation with the patch doesn't mention anything about doing this, I think it is at least not a bad idea to un-do the work-around for before upgrading:


                  connect /as sysdba

                  alter session set current_schema=MDSYS;

                  ASSOCIATE STATISTICS WITH INDEXTYPES spatial_index USING sdo_statistics;

                  ASSOCIATE STATISTICS WITH PACKAGES sdo_3gl USING sdo_statistics;

                  ASSOCIATE STATISTICS WITH PACKAGES prvt_idx USING sdo_statistics;


                  BTW - If anyone knows if that is included in the upgrade script (I would certainly hope so - but I don't see it), please chime in. This is for out-of-place upgrades.





                  • 21. Re: to upgrade = SLOW sdo_geometry return
                    davidp 2

                    Thank you Bryan.

                    I seem to be running into this problem quite hard, on spatial queries with a query to find the filtering geometry and some other filter clauses in my query.

                    Did you find any negative effects or side effects from the DISASSOCIATE STATISTICS commands ?




                    • 22. Re: to upgrade = SLOW sdo_geometry return
                      B Hall



                      The negative side effect is that with the DISASSOCIATE STATISTICS commands, the spatial index is always used.


                      While that is a good thing when you have selective queries, it is a very bad thing when you select all or a good portion of a table's data. It first finds the candidate in the index, uses the rowid to look up the row, and then returns it - over and over - instead of just doing a full table scan and discarding the few (if any) rows that do not fit the criteria.



                      • 23. Re: to upgrade = SLOW sdo_geometry return
                        davidp 2

                        OK. Thanks Bryan. I appreciate your follow up. It's helpful to know what sort of effects to expect.

                        1 2 Previous Next