 | |
Performance: SDO_RELATE vs. Stored Procedure
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Performance: SDO_RELATE vs. Stored Procedure -
11-19-2008
, 07:43 AM
|
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
#22
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare |
The reason I did not use any index stuctures is, that I wanted to
compare the performance of built-in functions with my own
implementations. Therefore, I run the following queries, which are
executed similarly (see execution plans below):
Q1: SELECT COUNT(*) FROM /*+ ordered */
oracle_triangles t1, oracle_triangles t2
WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) <>
'FALSE'
AND t1.id <> t2.id
Q2: SELECT COUNT(*) FROM /*+ ordered */
my_triangles t1, my_triangles d2
WHERE t1.contains(VALUE(t2)) = 1
AND t1.id <> t2.id;
Execution Plan for Q1:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 2068
SORT (AGGREGATE)
NESTED LOOPS ()
2068
TABLE ACCESS (FULL) oracle_triangles 6
TABLE ACCESS (FULL) oracle_triangles 4
Execution Plan for Q2:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 1801
SORT (AGGREGATE)
NESTED LOOPS ()
1801
TABLE ACCESS (FULL) my_triangles 5
TABLE ACCESS (FULL) my_triangles 4
In PostgreSQL I do not use any indexes either, so the queries also
involve a nested loops join using full table scans.
Although the experimental settings equal for each query and each dbms,
I still observe tremendous performance
differences that I just cannot explain.
Daniel
#23
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare |
The reason I did not use any index stuctures is, that I wanted to
compare the performance of built-in functions with my own
implementations. Therefore, I run the following queries, which are
executed similarly (see execution plans below):
Q1: SELECT COUNT(*) FROM /*+ ordered */
oracle_triangles t1, oracle_triangles t2
WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) <>
'FALSE'
AND t1.id <> t2.id
Q2: SELECT COUNT(*) FROM /*+ ordered */
my_triangles t1, my_triangles d2
WHERE t1.contains(VALUE(t2)) = 1
AND t1.id <> t2.id;
Execution Plan for Q1:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 2068
SORT (AGGREGATE)
NESTED LOOPS ()
2068
TABLE ACCESS (FULL) oracle_triangles 6
TABLE ACCESS (FULL) oracle_triangles 4
Execution Plan for Q2:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 1801
SORT (AGGREGATE)
NESTED LOOPS ()
1801
TABLE ACCESS (FULL) my_triangles 5
TABLE ACCESS (FULL) my_triangles 4
In PostgreSQL I do not use any indexes either, so the queries also
involve a nested loops join using full table scans.
Although the experimental settings equal for each query and each dbms,
I still observe tremendous performance
differences that I just cannot explain.
Daniel
#24
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare |
The reason I did not use any index stuctures is, that I wanted to
compare the performance of built-in functions with my own
implementations. Therefore, I run the following queries, which are
executed similarly (see execution plans below):
Q1: SELECT COUNT(*) FROM /*+ ordered */
oracle_triangles t1, oracle_triangles t2
WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) <>
'FALSE'
AND t1.id <> t2.id
Q2: SELECT COUNT(*) FROM /*+ ordered */
my_triangles t1, my_triangles d2
WHERE t1.contains(VALUE(t2)) = 1
AND t1.id <> t2.id;
Execution Plan for Q1:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 2068
SORT (AGGREGATE)
NESTED LOOPS ()
2068
TABLE ACCESS (FULL) oracle_triangles 6
TABLE ACCESS (FULL) oracle_triangles 4
Execution Plan for Q2:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 1801
SORT (AGGREGATE)
NESTED LOOPS ()
1801
TABLE ACCESS (FULL) my_triangles 5
TABLE ACCESS (FULL) my_triangles 4
In PostgreSQL I do not use any indexes either, so the queries also
involve a nested loops join using full table scans.
Although the experimental settings equal for each query and each dbms,
I still observe tremendous performance
differences that I just cannot explain.
Daniel
#25
| |||
| |||
|
|
schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare |
The reason I did not use any index stuctures is, that I wanted to
compare the performance of built-in functions with my own
implementations. Therefore, I run the following queries, which are
executed similarly (see execution plans below):
Q1: SELECT COUNT(*) FROM /*+ ordered */
oracle_triangles t1, oracle_triangles t2
WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) <>
'FALSE'
AND t1.id <> t2.id
Q2: SELECT COUNT(*) FROM /*+ ordered */
my_triangles t1, my_triangles d2
WHERE t1.contains(VALUE(t2)) = 1
AND t1.id <> t2.id;
Execution Plan for Q1:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 2068
SORT (AGGREGATE)
NESTED LOOPS ()
2068
TABLE ACCESS (FULL) oracle_triangles 6
TABLE ACCESS (FULL) oracle_triangles 4
Execution Plan for Q2:
Operation
Object COST
------------------------------
------------------------------ ----------
SELECT STATEMENT () 1801
SORT (AGGREGATE)
NESTED LOOPS ()
1801
TABLE ACCESS (FULL) my_triangles 5
TABLE ACCESS (FULL) my_triangles 4
In PostgreSQL I do not use any indexes either, so the queries also
involve a nested loops join using full table scans.
Although the experimental settings equal for each query and each dbms,
I still observe tremendous performance
differences that I just cannot explain.
Daniel
#26
| |||
| |||
|
|
On 19 Nov., 14:43, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare First of all: Thanks for your effort! I appreciate it. The reason I did not use any index stuctures is, that I wanted to compare the performance of built-in functions with my own implementations. Therefore, I run the following queries, which are executed similarly (see execution plans below): Q1: SELECT COUNT(*) FROM /*+ ordered */ oracle_triangles t1, oracle_triangles t2 WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) 'FALSE' AND t1.id <> t2.id Q2: SELECT COUNT(*) FROM /*+ ordered */ my_triangles t1, my_triangles d2 WHERE t1.contains(VALUE(t2)) = 1 AND t1.id <> t2.id; Execution Plan for Q1: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 2068 SORT (AGGREGATE) NESTED LOOPS () 2068 TABLE ACCESS (FULL) oracle_triangles 6 TABLE ACCESS (FULL) oracle_triangles 4 Execution Plan for Q2: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 1801 SORT (AGGREGATE) NESTED LOOPS () 1801 TABLE ACCESS (FULL) my_triangles 5 TABLE ACCESS (FULL) my_triangles 4 In PostgreSQL I do not use any indexes either, so the queries also involve a nested loops join using full table scans. Although the experimental settings equal for each query and each dbms, I still observe tremendous performance differences that I just cannot explain. Daniel |
It's like filling a car with the wrong fuel and wonder why it's so slow...
Or wonder why you can push a bike a lot faster than a car.
I'm sure your algorithm can't calculate a relation between geometries
with 62773 points... you'll find that SDO_GEOM.RELATE will hardly be any
slower than with the triangles.
/*+ ordered */ without indexes makes no sense here....
By the way: using Oracle Spatial (even with Oracle XE) requires an extra
license. Only Oracle Locator may be used for free with XE. This
explicitly applies to SDO_GEOM functions and procedures, with the
exception of
- SDO_GEOM.SDO_DISTANCE()
- SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
- SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT().
From the docs:
"
In general, Locator includes the data types, operators, and indexing
capabilities of Oracle Spatial, along with a limited set of the
functions and procedures of Spatial. The Locator features include the
following:
An object type (SDO_GEOMETRY) that describes and supports any type of
geometry
A spatial indexing capability that lets you create spatial indexes on
geometry data
Spatial operators (described in Chapter 11) that use the spatial index
for performing spatial queries
Some geometry functions and the SDO_AGGR_MBR spatial aggregate function
Coordinate system support for explicit geometry and layer
transformations (SDO_CS.TRANSFORM function and SDO_CS.TRANSFORM_LAYER
procedure, described in Chapter 13)
Tuning functions and procedures (SDO_TUNE package, described in Chapter 19)
Spatial utility functions (SDO_UTIL package, described in Chapter 20)
Integration with Oracle Application Server 10g
Much confusion exists about what is licensed and what is not with
Locator. What is actually licensed is clearly documented in Appendix B
of the Spatial User guide. Because, this article concentrates on the
SDO_GEOM package functions and procedures, we note that only those
functions listed in Table B-1 are licensed. These are:
SDO_GEOM.SDO_DISTANCE()
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
"
For the full story, see
http://www.spatialdbadvisor.com/orac...dogeom-package
or
http://download.oracle.com/docs/cd/B...r.htm#CFACCEEG
Shakespeare
#27
| |||
| |||
|
|
On 19 Nov., 14:43, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare First of all: Thanks for your effort! I appreciate it. The reason I did not use any index stuctures is, that I wanted to compare the performance of built-in functions with my own implementations. Therefore, I run the following queries, which are executed similarly (see execution plans below): Q1: SELECT COUNT(*) FROM /*+ ordered */ oracle_triangles t1, oracle_triangles t2 WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) 'FALSE' AND t1.id <> t2.id Q2: SELECT COUNT(*) FROM /*+ ordered */ my_triangles t1, my_triangles d2 WHERE t1.contains(VALUE(t2)) = 1 AND t1.id <> t2.id; Execution Plan for Q1: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 2068 SORT (AGGREGATE) NESTED LOOPS () 2068 TABLE ACCESS (FULL) oracle_triangles 6 TABLE ACCESS (FULL) oracle_triangles 4 Execution Plan for Q2: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 1801 SORT (AGGREGATE) NESTED LOOPS () 1801 TABLE ACCESS (FULL) my_triangles 5 TABLE ACCESS (FULL) my_triangles 4 In PostgreSQL I do not use any indexes either, so the queries also involve a nested loops join using full table scans. Although the experimental settings equal for each query and each dbms, I still observe tremendous performance differences that I just cannot explain. Daniel |
It's like filling a car with the wrong fuel and wonder why it's so slow...
Or wonder why you can push a bike a lot faster than a car.
I'm sure your algorithm can't calculate a relation between geometries
with 62773 points... you'll find that SDO_GEOM.RELATE will hardly be any
slower than with the triangles.
/*+ ordered */ without indexes makes no sense here....
By the way: using Oracle Spatial (even with Oracle XE) requires an extra
license. Only Oracle Locator may be used for free with XE. This
explicitly applies to SDO_GEOM functions and procedures, with the
exception of
- SDO_GEOM.SDO_DISTANCE()
- SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
- SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT().
From the docs:
"
In general, Locator includes the data types, operators, and indexing
capabilities of Oracle Spatial, along with a limited set of the
functions and procedures of Spatial. The Locator features include the
following:
An object type (SDO_GEOMETRY) that describes and supports any type of
geometry
A spatial indexing capability that lets you create spatial indexes on
geometry data
Spatial operators (described in Chapter 11) that use the spatial index
for performing spatial queries
Some geometry functions and the SDO_AGGR_MBR spatial aggregate function
Coordinate system support for explicit geometry and layer
transformations (SDO_CS.TRANSFORM function and SDO_CS.TRANSFORM_LAYER
procedure, described in Chapter 13)
Tuning functions and procedures (SDO_TUNE package, described in Chapter 19)
Spatial utility functions (SDO_UTIL package, described in Chapter 20)
Integration with Oracle Application Server 10g
Much confusion exists about what is licensed and what is not with
Locator. What is actually licensed is clearly documented in Appendix B
of the Spatial User guide. Because, this article concentrates on the
SDO_GEOM package functions and procedures, we note that only those
functions listed in Table B-1 are licensed. These are:
SDO_GEOM.SDO_DISTANCE()
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
"
For the full story, see
http://www.spatialdbadvisor.com/orac...dogeom-package
or
http://download.oracle.com/docs/cd/B...r.htm#CFACCEEG
Shakespeare
#28
| |||
| |||
|
|
On 19 Nov., 14:43, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare First of all: Thanks for your effort! I appreciate it. The reason I did not use any index stuctures is, that I wanted to compare the performance of built-in functions with my own implementations. Therefore, I run the following queries, which are executed similarly (see execution plans below): Q1: SELECT COUNT(*) FROM /*+ ordered */ oracle_triangles t1, oracle_triangles t2 WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) 'FALSE' AND t1.id <> t2.id Q2: SELECT COUNT(*) FROM /*+ ordered */ my_triangles t1, my_triangles d2 WHERE t1.contains(VALUE(t2)) = 1 AND t1.id <> t2.id; Execution Plan for Q1: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 2068 SORT (AGGREGATE) NESTED LOOPS () 2068 TABLE ACCESS (FULL) oracle_triangles 6 TABLE ACCESS (FULL) oracle_triangles 4 Execution Plan for Q2: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 1801 SORT (AGGREGATE) NESTED LOOPS () 1801 TABLE ACCESS (FULL) my_triangles 5 TABLE ACCESS (FULL) my_triangles 4 In PostgreSQL I do not use any indexes either, so the queries also involve a nested loops join using full table scans. Although the experimental settings equal for each query and each dbms, I still observe tremendous performance differences that I just cannot explain. Daniel |
It's like filling a car with the wrong fuel and wonder why it's so slow...
Or wonder why you can push a bike a lot faster than a car.
I'm sure your algorithm can't calculate a relation between geometries
with 62773 points... you'll find that SDO_GEOM.RELATE will hardly be any
slower than with the triangles.
/*+ ordered */ without indexes makes no sense here....
By the way: using Oracle Spatial (even with Oracle XE) requires an extra
license. Only Oracle Locator may be used for free with XE. This
explicitly applies to SDO_GEOM functions and procedures, with the
exception of
- SDO_GEOM.SDO_DISTANCE()
- SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
- SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT().
From the docs:
"
In general, Locator includes the data types, operators, and indexing
capabilities of Oracle Spatial, along with a limited set of the
functions and procedures of Spatial. The Locator features include the
following:
An object type (SDO_GEOMETRY) that describes and supports any type of
geometry
A spatial indexing capability that lets you create spatial indexes on
geometry data
Spatial operators (described in Chapter 11) that use the spatial index
for performing spatial queries
Some geometry functions and the SDO_AGGR_MBR spatial aggregate function
Coordinate system support for explicit geometry and layer
transformations (SDO_CS.TRANSFORM function and SDO_CS.TRANSFORM_LAYER
procedure, described in Chapter 13)
Tuning functions and procedures (SDO_TUNE package, described in Chapter 19)
Spatial utility functions (SDO_UTIL package, described in Chapter 20)
Integration with Oracle Application Server 10g
Much confusion exists about what is licensed and what is not with
Locator. What is actually licensed is clearly documented in Appendix B
of the Spatial User guide. Because, this article concentrates on the
SDO_GEOM package functions and procedures, we note that only those
functions listed in Table B-1 are licensed. These are:
SDO_GEOM.SDO_DISTANCE()
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
"
For the full story, see
http://www.spatialdbadvisor.com/orac...dogeom-package
or
http://download.oracle.com/docs/cd/B...r.htm#CFACCEEG
Shakespeare
#29
| |||
| |||
|
|
On 19 Nov., 14:43, Shakespeare <what... (AT) xs4all (DOT) nl> wrote: schae... (AT) googlemail (DOT) com schreef: 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 Did you run an explainplanfor yourquery? Did you create spatial indexes? Shakespeare First of all: Thanks for your effort! I appreciate it. The reason I did not use any index stuctures is, that I wanted to compare the performance of built-in functions with my own implementations. Therefore, I run the following queries, which are executed similarly (see execution plans below): Q1: SELECT COUNT(*) FROM /*+ ordered */ oracle_triangles t1, oracle_triangles t2 WHERE SDO_GEOM.RELATE(t1.triangle, 'contains', t2.triangle, 0.001) 'FALSE' AND t1.id <> t2.id Q2: SELECT COUNT(*) FROM /*+ ordered */ my_triangles t1, my_triangles d2 WHERE t1.contains(VALUE(t2)) = 1 AND t1.id <> t2.id; Execution Plan for Q1: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 2068 SORT (AGGREGATE) NESTED LOOPS () 2068 TABLE ACCESS (FULL) oracle_triangles 6 TABLE ACCESS (FULL) oracle_triangles 4 Execution Plan for Q2: Operation Object COST ------------------------------ ------------------------------ ---------- SELECT STATEMENT () 1801 SORT (AGGREGATE) NESTED LOOPS () 1801 TABLE ACCESS (FULL) my_triangles 5 TABLE ACCESS (FULL) my_triangles 4 In PostgreSQL I do not use any indexes either, so the queries also involve a nested loops join using full table scans. Although the experimental settings equal for each query and each dbms, I still observe tremendous performance differences that I just cannot explain. Daniel |
It's like filling a car with the wrong fuel and wonder why it's so slow...
Or wonder why you can push a bike a lot faster than a car.
I'm sure your algorithm can't calculate a relation between geometries
with 62773 points... you'll find that SDO_GEOM.RELATE will hardly be any
slower than with the triangles.
/*+ ordered */ without indexes makes no sense here....
By the way: using Oracle Spatial (even with Oracle XE) requires an extra
license. Only Oracle Locator may be used for free with XE. This
explicitly applies to SDO_GEOM functions and procedures, with the
exception of
- SDO_GEOM.SDO_DISTANCE()
- SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
- SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT().
From the docs:
"
In general, Locator includes the data types, operators, and indexing
capabilities of Oracle Spatial, along with a limited set of the
functions and procedures of Spatial. The Locator features include the
following:
An object type (SDO_GEOMETRY) that describes and supports any type of
geometry
A spatial indexing capability that lets you create spatial indexes on
geometry data
Spatial operators (described in Chapter 11) that use the spatial index
for performing spatial queries
Some geometry functions and the SDO_AGGR_MBR spatial aggregate function
Coordinate system support for explicit geometry and layer
transformations (SDO_CS.TRANSFORM function and SDO_CS.TRANSFORM_LAYER
procedure, described in Chapter 13)
Tuning functions and procedures (SDO_TUNE package, described in Chapter 19)
Spatial utility functions (SDO_UTIL package, described in Chapter 20)
Integration with Oracle Application Server 10g
Much confusion exists about what is licensed and what is not with
Locator. What is actually licensed is clearly documented in Appendix B
of the Spatial User guide. Because, this article concentrates on the
SDO_GEOM package functions and procedures, we note that only those
functions listed in Table B-1 are licensed. These are:
SDO_GEOM.SDO_DISTANCE()
SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT()
SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT()
"
For the full story, see
http://www.spatialdbadvisor.com/orac...dogeom-package
or
http://download.oracle.com/docs/cd/B...r.htm#CFACCEEG
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.