Re: Performance: SDO_RELATE vs. Stored Procedure
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 19 Nov 2008 14:43:11 +0100
Message-ID: <492417f5$0$193$e4fe514c@news.xs4all.nl>
>> 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
Date: Wed, 19 Nov 2008 14:43:11 +0100
Message-ID: <492417f5$0$193$e4fe514c@news.xs4all.nl>
schaef2k_at_googlemail.com schreef:
> 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
Did you run an explain plan for your query? Did you create spatial indexes?
Shakespeare Received on Wed Nov 19 2008 - 07:43:11 CST