 | |
Performance: SDO_RELATE vs. Stored Procedure
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Performance: SDO_RELATE vs. Stored Procedure -
11-16-2008
, 05:10 PM
|
On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
Shakespeare
#12
| |||
| |||
|
|
On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
Shakespeare
#13
| |||
| |||
|
|
On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
Shakespeare
#14
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
seconds.
Daniel
#15
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
seconds.
Daniel
#16
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
seconds.
Daniel
#17
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 |
seconds.
Daniel
#18
| |||
| |||
|
|
On 16 Nov., 23:10, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 Unfortunately, it did not help. Still the runtime is round about 100 seconds. Daniel |
Shakespeare
#19
| |||
| |||
|
|
On 16 Nov., 23:10, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 Unfortunately, it did not help. Still the runtime is round about 100 seconds. Daniel |
Shakespeare
#20
| |||
| |||
|
|
On 16 Nov., 23:10, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: On 15 Nov., 20:00, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) 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 Unfortunately, it did not help. Still the runtime is round about 100 seconds. Daniel |
Shakespeare
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.