Performance: SDO_RELATE vs. Stored Procedure

From: <>
Date: Sat, 15 Nov 2008 02:43:30 -0800 (PST)
Message-ID: <>


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 builtin  functions?

Thanks in advance!

Daniel Received on Sat Nov 15 2008 - 04:43:30 CST

Original text of this message