Re Performance SDO_RELATE vs. Stored Procedure by pyw18970

VIEWS: 0 PAGES: 4

									                      Re: Performance: SDO_RELATE vs. Stored Procedure

Re: Performance: SDO_RELATE vs. Stored
Procedure

Source:
http://newsgroups.derkeiler.com/Archive/Comp/comp.databases.oracle.misc/2008−11/msg00110.html



     • From: Shakespeare <whatsin@xxxxxxxxx>
     • Date: Wed, 19 Nov 2008 14:43:11 +0100

schaef2k@xxxxxxxxxxxxxx schreef:

       On 16 Nov., 23:10, Shakespeare <what...@xxxxxxxxx> wrote:

              schae...@xxxxxxxxxxxxxx schreef:




                      On 15 Nov., 20:00, Shakespeare <what...@xxxxxxxxx>
                      wrote:

                             schae...@xxxxxxxxxxxxxx schreef:

                                     Hi,
                                     I'm relatively new to Oracle
                                     and as I am experiencing
                                     some
                                     inexplicable results I ask for
                                     your help.
                                     In Oracle 10g XE I created a
                                     datatype for triangle−objects
                                     consisting
                                     of 3 points, a name and a
                                     member function contains(t
                                     Triangle). An
                                     object−table contains 500
                                     randomly created triangles.
                                     By means of
                                     additional static functions
                                     these triangles are converted
                                     to valid
                                     SDO_GEOMETRY objects,
                                     which are stored in another
                                     table.
                                     Now, evaluating a self−join
                                     of each table w.r.t. the

Re: Performance: SDO_RELATE vs. Stored Procedure                                                1
                   Re: Performance: SDO_RELATE vs. Stored Procedure
                                   predicate
                                   "triangle a contains triangle
                                   b" I expected the
                                   SDO_GEOM.SDO_RELATE
                                   operator to run faster than
                                   my own contains−operator,
                                   since it is a
                                   built in function.
                                   In contrast, it performs 3
                                   times slower
                                   (~100seconds/450 results vs
                                   ~35seconds/450 results).
                                   Why is that?
                                   Does the SDO_RELATE
                                   operator perform some kind
                                   of filter/refine step,
                                   i.e. test the spatial
                                   relationship of the
                                   geometries' bounding
                                   rectangle first (my own
                                   contains operator omits such
                                   a step)? Is there
                                   any way to get more
                                   implementation specific
                                   documentation about built−
                                   in functions?
                                   Thanks in advance!
                                   Daniel

                           One of the possiblities why your proc is
                           faster is that you already KNOW
                           your geometries are triangular, where
                           SDO_RELATE is for all kinds of
                           geometries. And SDO_RELATE was built
                           to do more than CONTAINS only
                           But there's more to it: SDO_RELATE highly
                           depends on spatial indexes.
                           Take a look at Oracle Spatial
                           Documentation.
                           Shakespeare

                   OK, in the Oracle Spatial documentation it says:
                   "OVERLAPBDYDISJOINT can be defined as the relation
                   where the objects
                   overlap but the boundaries are disjoint. This functionality is
                   made
                   available
                   through an operator, SDO_RELATE, and a function,
                   SDO_GEOM.RELATE().
                   The operator, SDO_RELATE, is registered with the
                   extensible optimizer

Re: Performance: SDO_RELATE vs. Stored Procedure                                    2
                       Re: Performance: SDO_RELATE vs. Stored Procedure
                      and
                      hence the optimizer will evaluate various query plans that
                      include or
                      exclude the
                      use of a spatial index. The function, SDO_GEOM.RELATE,
                      does not use
                      the
                      spatial index and simply evaluates the two geometries that
                      are passed
                      to it via
                      the argument list for the specified topological
                      relationship.[...]"
                      But I still wonder, why there is such a huge performance
                      difference
                      between my PL/SQL
                      code and the built−in Function. I ran the same test in
                      PostgreSQL/
                      PostGIS, i.e. I compared
                      my own contains method with PostGIS's spatial containment
                      operator. As
                      for the oracle
                      operator, contains() may not use any spatial index structure
                      in my
                      test and of course it
                      may not use any optimizations that could apply to the
                      processing of
                      triangles.
                      Both built−in operators base on the computation of the
                      intersection
                      matrix as defined by
                      the 9−intersection model, so I suppose them to be somehow
                      similar.
                      However, the postgres
                      operator returns its results in less than 1 second (450 rows),
                      whereas
                      my plpgsql−code runs
                      for ~15 seconds and the oracle operator still needs
                      ~100seconds.
                      Although a small performance
                      advantage over oracle was expected, I didn't expect it so
                      huge and it
                      leaves me even more
                      confused.
                      Daniel

              Try it using an /*+ ordered */ hint. This may help!

              Shakespeare


      Unfortunately, it did not help. Still the runtime is round about 100

Re: Performance: SDO_RELATE vs. Stored Procedure                                       3
                        Re: Performance: SDO_RELATE vs. Stored Procedure

        seconds.

        Daniel


Did you run an explain plan for your query? Did you create spatial indexes?

Shakespeare
.




Re: Performance: SDO_RELATE vs. Stored Procedure                              4

								
To top