 | |
Oracle Spatial SDO Aggregate Union fails with ORA-29913ODCIAGGREGATEMERGE
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913ODCIAGGREGATEMERGE -
06-16-2008
, 02:16 AM
|
----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare |
But, when i rewrite the query i get the following error:
SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Voer wachtwoord in:
Verbonden met:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
4 FROM (
5 -- with parent
6 SELECT
7 DISTINCT(GUID),
8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
9 FROM
10 (
11 SELECT
12 MULTI.PARENT AS GUID,
13 TOPO.GEOMETRIE AS GEOMETRIE
14 FROM DGDTW_TOPOGRAFIE TOPO
15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
17 INNER JOIN DGDTW_PARENT_CHILD MULTI
18 ON MULTI.CHILD = GUIDS.ID
19 AND MULTI.ARCHIVE IS NULL
20 WHERE TOPO.VERVAL IS NULL
21 AND NOT TOPO.PARAMETERS = 515
22 ORDER BY GUID
23 ) LINES
24 GROUP BY GUID
25 UNION ALL
26 -- without parent
27 (
28 SELECT
29 DISTINCT(GUID),
30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
31 FROM
32 (
33 SELECT
34 GUIDS.ID AS GUID,
35 TOPO.GEOMETRIE AS GEOMETRIE
36 FROM DGDTW_TOPOGRAFIE TOPO
37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
39 WHERE TOPO.VERVAL IS NULL
40 AND NOT TOPO.PARAMETERS = 515
41 AND GUIDS.ID NOT IN (SELECT CHILD FROM
DGDTW_PARENT_CHILD)
42 ORDER BY GUID
43 ) LINES
44 GROUP BY GUID
45 )
46 ) UNIONLINES
47 GROUP BY GUID
48 ;
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
Er zijn geen rijen geselecteerd.
SQL>
The following query works, so something goes wrong on doing the
distinct over both union results. Any thoughts?
SELECT
DISTINCT(GUID),
COUNT(*)
FROM (
-- with parent
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
FROM
(
SELECT
MULTI.PARENT AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
INNER JOIN DGDTW_PARENT_CHILD MULTI
ON MULTI.CHILD = GUIDS.ID
AND MULTI.ARCHIVE IS NULL
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
ORDER BY GUID
) LINES
GROUP BY GUID
UNION ALL
-- without parent
(
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
FROM
(
SELECT
GUIDS.ID AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD)
ORDER BY GUID
) LINES
GROUP BY GUID
)
) UNIONLINES
GROUP BY GUID )
#32
| |||
| |||
|
|
----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare |
But, when i rewrite the query i get the following error:
SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Voer wachtwoord in:
Verbonden met:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
4 FROM (
5 -- with parent
6 SELECT
7 DISTINCT(GUID),
8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
9 FROM
10 (
11 SELECT
12 MULTI.PARENT AS GUID,
13 TOPO.GEOMETRIE AS GEOMETRIE
14 FROM DGDTW_TOPOGRAFIE TOPO
15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
17 INNER JOIN DGDTW_PARENT_CHILD MULTI
18 ON MULTI.CHILD = GUIDS.ID
19 AND MULTI.ARCHIVE IS NULL
20 WHERE TOPO.VERVAL IS NULL
21 AND NOT TOPO.PARAMETERS = 515
22 ORDER BY GUID
23 ) LINES
24 GROUP BY GUID
25 UNION ALL
26 -- without parent
27 (
28 SELECT
29 DISTINCT(GUID),
30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
31 FROM
32 (
33 SELECT
34 GUIDS.ID AS GUID,
35 TOPO.GEOMETRIE AS GEOMETRIE
36 FROM DGDTW_TOPOGRAFIE TOPO
37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
39 WHERE TOPO.VERVAL IS NULL
40 AND NOT TOPO.PARAMETERS = 515
41 AND GUIDS.ID NOT IN (SELECT CHILD FROM
DGDTW_PARENT_CHILD)
42 ORDER BY GUID
43 ) LINES
44 GROUP BY GUID
45 )
46 ) UNIONLINES
47 GROUP BY GUID
48 ;
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
Er zijn geen rijen geselecteerd.
SQL>
The following query works, so something goes wrong on doing the
distinct over both union results. Any thoughts?
SELECT
DISTINCT(GUID),
COUNT(*)
FROM (
-- with parent
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
FROM
(
SELECT
MULTI.PARENT AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
INNER JOIN DGDTW_PARENT_CHILD MULTI
ON MULTI.CHILD = GUIDS.ID
AND MULTI.ARCHIVE IS NULL
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
ORDER BY GUID
) LINES
GROUP BY GUID
UNION ALL
-- without parent
(
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
FROM
(
SELECT
GUIDS.ID AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD)
ORDER BY GUID
) LINES
GROUP BY GUID
)
) UNIONLINES
GROUP BY GUID )
#33
| |||
| |||
|
|
----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare |
But, when i rewrite the query i get the following error:
SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Voer wachtwoord in:
Verbonden met:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> SELECT
2 DISTINCT(GUID),
3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
4 FROM (
5 -- with parent
6 SELECT
7 DISTINCT(GUID),
8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
9 FROM
10 (
11 SELECT
12 MULTI.PARENT AS GUID,
13 TOPO.GEOMETRIE AS GEOMETRIE
14 FROM DGDTW_TOPOGRAFIE TOPO
15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
17 INNER JOIN DGDTW_PARENT_CHILD MULTI
18 ON MULTI.CHILD = GUIDS.ID
19 AND MULTI.ARCHIVE IS NULL
20 WHERE TOPO.VERVAL IS NULL
21 AND NOT TOPO.PARAMETERS = 515
22 ORDER BY GUID
23 ) LINES
24 GROUP BY GUID
25 UNION ALL
26 -- without parent
27 (
28 SELECT
29 DISTINCT(GUID),
30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
31 FROM
32 (
33 SELECT
34 GUIDS.ID AS GUID,
35 TOPO.GEOMETRIE AS GEOMETRIE
36 FROM DGDTW_TOPOGRAFIE TOPO
37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS
38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
39 WHERE TOPO.VERVAL IS NULL
40 AND NOT TOPO.PARAMETERS = 515
41 AND GUIDS.ID NOT IN (SELECT CHILD FROM
DGDTW_PARENT_CHILD)
42 ORDER BY GUID
43 ) LINES
44 GROUP BY GUID
45 )
46 ) UNIONLINES
47 GROUP BY GUID
48 ;
ERROR:
ORA-03113: Einde-van-bestand op communicatiekanaal.
Er zijn geen rijen geselecteerd.
SQL>
The following query works, so something goes wrong on doing the
distinct over both union results. Any thoughts?
SELECT
DISTINCT(GUID),
COUNT(*)
FROM (
-- with parent
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE
FROM
(
SELECT
MULTI.PARENT AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
INNER JOIN DGDTW_PARENT_CHILD MULTI
ON MULTI.CHILD = GUIDS.ID
AND MULTI.ARCHIVE IS NULL
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
ORDER BY GUID
) LINES
GROUP BY GUID
UNION ALL
-- without parent
(
SELECT
DISTINCT(GUID),
SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS
GEOMETRIE
FROM
(
SELECT
GUIDS.ID AS GUID,
TOPO.GEOMETRIE AS GEOMETRIE
FROM DGDTW_TOPOGRAFIE TOPO
INNER JOIN DGDTW_OBJECTGUIDS GUIDS
ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID
WHERE TOPO.VERVAL IS NULL
AND NOT TOPO.PARAMETERS = 515
AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD)
ORDER BY GUID
) LINES
GROUP BY GUID
)
) UNIONLINES
GROUP BY GUID )
#34
| |||
| |||
|
|
On Jun 12, 6:53 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare Progress! But, when i rewrite the query i get the following error: SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Voer wachtwoord in: Verbonden met: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 4 FROM ( 5 -- with parent 6 SELECT 7 DISTINCT(GUID), 8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 9 FROM 10 ( 11 SELECT 12 MULTI.PARENT AS GUID, 13 TOPO.GEOMETRIE AS GEOMETRIE 14 FROM DGDTW_TOPOGRAFIE TOPO 15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 17 INNER JOIN DGDTW_PARENT_CHILD MULTI 18 ON MULTI.CHILD = GUIDS.ID 19 AND MULTI.ARCHIVE IS NULL 20 WHERE TOPO.VERVAL IS NULL 21 AND NOT TOPO.PARAMETERS = 515 22 ORDER BY GUID 23 ) LINES 24 GROUP BY GUID 25 UNION ALL 26 -- without parent 27 ( 28 SELECT 29 DISTINCT(GUID), 30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 31 FROM 32 ( 33 SELECT 34 GUIDS.ID AS GUID, 35 TOPO.GEOMETRIE AS GEOMETRIE 36 FROM DGDTW_TOPOGRAFIE TOPO 37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 39 WHERE TOPO.VERVAL IS NULL 40 AND NOT TOPO.PARAMETERS = 515 41 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) 42 ORDER BY GUID 43 ) LINES 44 GROUP BY GUID 45 ) 46 ) UNIONLINES 47 GROUP BY GUID 48 ; ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. Er zijn geen rijen geselecteerd. SQL The following query works, so something goes wrong on doing the distinct over both union results. Any thoughts? SELECT DISTINCT(GUID), COUNT(*) FROM ( -- with parent SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT MULTI.PARENT AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID INNER JOIN DGDTW_PARENT_CHILD MULTI ON MULTI.CHILD = GUIDS.ID AND MULTI.ARCHIVE IS NULL WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 ORDER BY GUID ) LINES GROUP BY GUID UNION ALL -- without parent ( SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT GUIDS.ID AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) ORDER BY GUID ) LINES GROUP BY GUID ) ) UNIONLINES GROUP BY GUID ) |
known for causing ora-03113, I have seen this happening with spatial
queries.
I also know there might be a problem with calling the SDO_AGGR_UNION twice
in one query, caused by caching or something like that. I have seen spatial
queries where we explicitly had to flush the SGA between calls to get the
right and consistent results.
Spatial is something special... we even had to move our tables to a
different platform (Windows!) to perform certain queries because they
failed on our platform (but that used to be Alpha-VMS). There are (were)
many OS-related bugs. You might even consider moving to a more recent
version of the DB.
What happens if you perform the queries on both of the parts of the union
seperately?
Shakespeare
#35
| |||
| |||
|
|
On Jun 12, 6:53 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare Progress! But, when i rewrite the query i get the following error: SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Voer wachtwoord in: Verbonden met: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 4 FROM ( 5 -- with parent 6 SELECT 7 DISTINCT(GUID), 8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 9 FROM 10 ( 11 SELECT 12 MULTI.PARENT AS GUID, 13 TOPO.GEOMETRIE AS GEOMETRIE 14 FROM DGDTW_TOPOGRAFIE TOPO 15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 17 INNER JOIN DGDTW_PARENT_CHILD MULTI 18 ON MULTI.CHILD = GUIDS.ID 19 AND MULTI.ARCHIVE IS NULL 20 WHERE TOPO.VERVAL IS NULL 21 AND NOT TOPO.PARAMETERS = 515 22 ORDER BY GUID 23 ) LINES 24 GROUP BY GUID 25 UNION ALL 26 -- without parent 27 ( 28 SELECT 29 DISTINCT(GUID), 30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 31 FROM 32 ( 33 SELECT 34 GUIDS.ID AS GUID, 35 TOPO.GEOMETRIE AS GEOMETRIE 36 FROM DGDTW_TOPOGRAFIE TOPO 37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 39 WHERE TOPO.VERVAL IS NULL 40 AND NOT TOPO.PARAMETERS = 515 41 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) 42 ORDER BY GUID 43 ) LINES 44 GROUP BY GUID 45 ) 46 ) UNIONLINES 47 GROUP BY GUID 48 ; ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. Er zijn geen rijen geselecteerd. SQL The following query works, so something goes wrong on doing the distinct over both union results. Any thoughts? SELECT DISTINCT(GUID), COUNT(*) FROM ( -- with parent SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT MULTI.PARENT AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID INNER JOIN DGDTW_PARENT_CHILD MULTI ON MULTI.CHILD = GUIDS.ID AND MULTI.ARCHIVE IS NULL WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 ORDER BY GUID ) LINES GROUP BY GUID UNION ALL -- without parent ( SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT GUIDS.ID AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) ORDER BY GUID ) LINES GROUP BY GUID ) ) UNIONLINES GROUP BY GUID ) |
known for causing ora-03113, I have seen this happening with spatial
queries.
I also know there might be a problem with calling the SDO_AGGR_UNION twice
in one query, caused by caching or something like that. I have seen spatial
queries where we explicitly had to flush the SGA between calls to get the
right and consistent results.
Spatial is something special... we even had to move our tables to a
different platform (Windows!) to perform certain queries because they
failed on our platform (but that used to be Alpha-VMS). There are (were)
many OS-related bugs. You might even consider moving to a more recent
version of the DB.
What happens if you perform the queries on both of the parts of the union
seperately?
Shakespeare
#36
| |||
| |||
|
|
On Jun 12, 6:53 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare Progress! But, when i rewrite the query i get the following error: SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Voer wachtwoord in: Verbonden met: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 4 FROM ( 5 -- with parent 6 SELECT 7 DISTINCT(GUID), 8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 9 FROM 10 ( 11 SELECT 12 MULTI.PARENT AS GUID, 13 TOPO.GEOMETRIE AS GEOMETRIE 14 FROM DGDTW_TOPOGRAFIE TOPO 15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 17 INNER JOIN DGDTW_PARENT_CHILD MULTI 18 ON MULTI.CHILD = GUIDS.ID 19 AND MULTI.ARCHIVE IS NULL 20 WHERE TOPO.VERVAL IS NULL 21 AND NOT TOPO.PARAMETERS = 515 22 ORDER BY GUID 23 ) LINES 24 GROUP BY GUID 25 UNION ALL 26 -- without parent 27 ( 28 SELECT 29 DISTINCT(GUID), 30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 31 FROM 32 ( 33 SELECT 34 GUIDS.ID AS GUID, 35 TOPO.GEOMETRIE AS GEOMETRIE 36 FROM DGDTW_TOPOGRAFIE TOPO 37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 39 WHERE TOPO.VERVAL IS NULL 40 AND NOT TOPO.PARAMETERS = 515 41 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) 42 ORDER BY GUID 43 ) LINES 44 GROUP BY GUID 45 ) 46 ) UNIONLINES 47 GROUP BY GUID 48 ; ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. Er zijn geen rijen geselecteerd. SQL The following query works, so something goes wrong on doing the distinct over both union results. Any thoughts? SELECT DISTINCT(GUID), COUNT(*) FROM ( -- with parent SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT MULTI.PARENT AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID INNER JOIN DGDTW_PARENT_CHILD MULTI ON MULTI.CHILD = GUIDS.ID AND MULTI.ARCHIVE IS NULL WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 ORDER BY GUID ) LINES GROUP BY GUID UNION ALL -- without parent ( SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT GUIDS.ID AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) ORDER BY GUID ) LINES GROUP BY GUID ) ) UNIONLINES GROUP BY GUID ) |
known for causing ora-03113, I have seen this happening with spatial
queries.
I also know there might be a problem with calling the SDO_AGGR_UNION twice
in one query, caused by caching or something like that. I have seen spatial
queries where we explicitly had to flush the SGA between calls to get the
right and consistent results.
Spatial is something special... we even had to move our tables to a
different platform (Windows!) to perform certain queries because they
failed on our platform (but that used to be Alpha-VMS). There are (were)
many OS-related bugs. You might even consider moving to a more recent
version of the DB.
What happens if you perform the queries on both of the parts of the union
seperately?
Shakespeare
#37
| |||
| |||
|
|
On Jun 12, 6:53 pm, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ----------------------------------------------------------------- Rethinking the case, for a test: what happens when you replace the left outer join with an inner join? (Besides not getting all the results) Shakespeare Progress! But, when i rewrite the query i get the following error: SQL*Plus: Release 9.2.0.1.0 - Production on Ma Jun 16 09:08:07 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Voer wachtwoord in: Verbonden met: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 4 FROM ( 5 -- with parent 6 SELECT 7 DISTINCT(GUID), 8 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 9 FROM 10 ( 11 SELECT 12 MULTI.PARENT AS GUID, 13 TOPO.GEOMETRIE AS GEOMETRIE 14 FROM DGDTW_TOPOGRAFIE TOPO 15 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 16 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 17 INNER JOIN DGDTW_PARENT_CHILD MULTI 18 ON MULTI.CHILD = GUIDS.ID 19 AND MULTI.ARCHIVE IS NULL 20 WHERE TOPO.VERVAL IS NULL 21 AND NOT TOPO.PARAMETERS = 515 22 ORDER BY GUID 23 ) LINES 24 GROUP BY GUID 25 UNION ALL 26 -- without parent 27 ( 28 SELECT 29 DISTINCT(GUID), 30 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE 31 FROM 32 ( 33 SELECT 34 GUIDS.ID AS GUID, 35 TOPO.GEOMETRIE AS GEOMETRIE 36 FROM DGDTW_TOPOGRAFIE TOPO 37 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 38 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 39 WHERE TOPO.VERVAL IS NULL 40 AND NOT TOPO.PARAMETERS = 515 41 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) 42 ORDER BY GUID 43 ) LINES 44 GROUP BY GUID 45 ) 46 ) UNIONLINES 47 GROUP BY GUID 48 ; ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. Er zijn geen rijen geselecteerd. SQL The following query works, so something goes wrong on doing the distinct over both union results. Any thoughts? SELECT DISTINCT(GUID), COUNT(*) FROM ( -- with parent SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT MULTI.PARENT AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID INNER JOIN DGDTW_PARENT_CHILD MULTI ON MULTI.CHILD = GUIDS.ID AND MULTI.ARCHIVE IS NULL WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 ORDER BY GUID ) LINES GROUP BY GUID UNION ALL -- without parent ( SELECT DISTINCT(GUID), SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.001)) AS GEOMETRIE FROM ( SELECT GUIDS.ID AS GUID, TOPO.GEOMETRIE AS GEOMETRIE FROM DGDTW_TOPOGRAFIE TOPO INNER JOIN DGDTW_OBJECTGUIDS GUIDS ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID WHERE TOPO.VERVAL IS NULL AND NOT TOPO.PARAMETERS = 515 AND GUIDS.ID NOT IN (SELECT CHILD FROM DGDTW_PARENT_CHILD) ORDER BY GUID ) LINES GROUP BY GUID ) ) UNIONLINES GROUP BY GUID ) |
known for causing ora-03113, I have seen this happening with spatial
queries.
I also know there might be a problem with calling the SDO_AGGR_UNION twice
in one query, caused by caching or something like that. I have seen spatial
queries where we explicitly had to flush the SGA between calls to get the
right and consistent results.
Spatial is something special... we even had to move our tables to a
different platform (Windows!) to perform certain queries because they
failed on our platform (but that used to be Alpha-VMS). There are (were)
many OS-related bugs. You might even consider moving to a more recent
version of the DB.
What happens if you perform the queries on both of the parts of the union
seperately?
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.