Re: Performance: SDO_RELATE vs. Stored Procedure

On 16 Nov., 23:10, Shakespeare <what..._at_xs4all.nl> wrote:
> schae..._at_googlemail.com schreef:
>
>
>
> > 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
>
> Try it using an /*+ ordered */ hint. This may help!
>
> Shakespeare

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

Daniel Received on Mon Nov 17 2008 - 11:23:56 CST