4 Replies Latest reply: Jan 22, 2013 5:59 AM by B Hall RSS

    Performance

    SeshuNaresh
      Hello,

      I have observered that the performance of sdo_relate is much more incase of 11.2.0.1 than 11.2.03.
      I am using R-Tree index and have around 125000 polygon records.

      thanks
        • 1. Re: Performance
          B Hall
          Have you applied the work-around for bug 9743250?

          If so - please provide execution plan details of both and more details so we can help you.

          Bryan
          • 2. Re: Performance
            SeshuNaresh
            No.. i do not know about the bug.
            I have used a simple query
            select * from geodata where sdo_relate(shape,geometry,mask='ANYINTERACT')='TRUE'
            and i dont get any results in 11.2.0.3 oracle database. I get result if I query it on 11.2.0.1.
            • 3. Re: Performance
              SeshuNaresh
              hello,

              Is this the workaround for the bug.

              SQL> connect /as sysdba
              SQL> alter session set current_schema=MDSYS;
              SQL> DISASSOCIATE STATISTICS FROM INDEXTYPES
              spatial_index FORCE;
              SQL> DISASSOCIATE STATISTICS FROM PACKAGES sdo_3gl
              FORCE;
              SQL> DISASSOCIATE STATISTICS FROM PACKAGES prvt_idx
              FORCE;

              thanks
              • 4. Re: Performance
                B Hall
                Yes, that is it.

                This will force Oracle to always use the index instead of using a FTS (which is normally what you want). However, if you ask for a large percentage of the data, it will then be slower.

                Bryan