Re: Performance: SDO_RELATE vs. Stored Procedure

On 15 Nov., 20:00, Shakespeare <what..._at_xs4all.nl> wrote:
> schae..._at_googlemail.com 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 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 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 Received on Sat Nov 15 2008 - 14:31:42 CST