Re: Performance: SDO_RELATE vs. Stored Procedure

From: Shakespeare <>
Date: Sun, 16 Nov 2008 23:10:36 +0100
Message-ID: <49209a5f$0$187$> schreef:
> On 15 Nov., 20:00, Shakespeare <> wrote:

>> 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 Received on Sun Nov 16 2008 - 16:10:36 CST

Original text of this message