Re: Performance: SDO_RELATE vs. Stored Procedure

From: Shakespeare <>
Date: Sat, 15 Nov 2008 20:00:56 +0100
Message-ID: <491f1c6a$0$195$> 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