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

schaef2k (AT) googlemail (DOT) com schreef:
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

schaef2k (AT) googlemail (DOT) com schreef:
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

schaef2k (AT) googlemail (DOT) com schreef:
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

schaef2k (AT) googlemail (DOT) com schreef:
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

On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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

On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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

On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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

On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
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

schaef2k (AT) googlemail (DOT) com schreef:
Try it using an /*+ ordered */ hint. This may help!

Shakespeare