Re: Performance: SDO_RELATE vs. Stored Procedure
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sun, 16 Nov 2008 23:10:36 +0100
Message-ID: <49209a5f$0$187$e4fe514c@news.xs4all.nl>
>
> 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
>
>
Date: Sun, 16 Nov 2008 23:10:36 +0100
Message-ID: <49209a5f$0$187$e4fe514c@news.xs4all.nl>
schaef2k_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 Received on Sun Nov 16 2008 - 16:10:36 CST