Re: Performance: SDO_RELATE vs. Stored Procedure

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 15 Nov 2008 20:00:56 +0100
Message-ID: <491f1c6a$0$195$e4fe514c@news.xs4all.nl>


schaef2k_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 Received on Sat Nov 15 2008 - 13:00:56 CST

Original text of this message