Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE
in [Oracle Help]
|
From: Eduard Witteveen on 12 Jun 2008 11:26 On Jun 12, 3:04 pm, "Shakespeare" <what...(a)xs4all.nl> wrote: > ... > ORA-29913 may have something to do with a too small tolerance setting. Try a > larger one and see what happens. Look at the end for output of the tolerance's > What OS are you on? The server is running on: cpu: 3.40 GHZ intel xeon(hyperthreaded) ram: 4032mb os: Windows 2003 SP servicepack: 2 <tolorance10cm> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; SELECT * FOUT in regel 1: .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance10cm> <tolorance1m> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {001FA2FC-1448-42C5-BC5F-480AD7392891} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) {003E7376-7418-4361-A139-F58251E06127} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(252697,14, 556409,2, 252690,788, 556404,936, 252711,182, 556400,086, 252716,6 7, 556404,21, 252697,14, 556409,2)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {00708284-7AB4-4CCC-82D1-CE86BD38777F} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247689,96, 558009,63, 247722,7, 558019,81, 247722,152, 558021,61, 247689,42, 558011,33, 247689,96, 558009,63)) {00832FCD-A3D6-4422-BDE1-BE937C0740E8} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(241710,281, 551011,756, 241711,873, 551007,536, 241720,75, 551012,4, 241735,7 ................................ ................................ ................................ SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(244241,1, 564379,01, 244242,76, 564376,3, 244259,59, 564386,54, 244251,37, 56 4400,14, 244245,54, 564396,48, 244251,6, 564385,54, 244241,1, 564379,01)) {7A42B22C-F30D-460C-8615-8B6083BBB3F7} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247852,537, 558231,679, 247850,79, 558231,19, 247855,48, 558215,5, 247862,903 , 558191,244, 247866,38, 558179,88, 247868,317, 558180,469, 247864,422, 558193,2 38, 247863,5, 558196,26, 247852,537, 558231,679)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {7A687E8B-B8EC-41D9-80CB-3EC2C2C44EF2} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. 375 rijen zijn geselecteerd. SQL> </tolorance1m> <tolorance1cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, .01)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance1cm-withoutcheck> <tolorance10cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: .ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEITERATE. ORA-13347: De co÷rdinaten voor een boog mogen niet gelijk zijn. <tolorance10cm-withoutcheck>
From: Shakespeare on 12 Jun 2008 12:32 "Eduard Witteveen" <eywitteveen(a)gmail.com> schreef in bericht news:f26766d3-a914-4f72-96d9-05226fc4bb5e(a)c58g2000hsc.googlegroups.com... On Jun 12, 3:04 pm, "Shakespeare" <what...(a)xs4all.nl> wrote: > ... > ORA-29913 may have something to do with a too small tolerance setting. Try > a > larger one and see what happens. Look at the end for output of the tolerance's > What OS are you on? The server is running on: cpu: 3.40 GHZ intel xeon(hyperthreaded) ram: 4032mb os: Windows 2003 SP servicepack: 2 <tolorance10cm> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance10cm> <tolorance1m> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {001FA2FC-1448-42C5-BC5F-480AD7392891} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) {003E7376-7418-4361-A139-F58251E06127} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(252697,14, 556409,2, 252690,788, 556404,936, 252711,182, 556400,086, 252716,6 7, 556404,21, 252697,14, 556409,2)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {00708284-7AB4-4CCC-82D1-CE86BD38777F} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247689,96, 558009,63, 247722,7, 558019,81, 247722,152, 558021,61, 247689,42, 558011,33, 247689,96, 558009,63)) {00832FCD-A3D6-4422-BDE1-BE937C0740E8} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(241710,281, 551011,756, 241711,873, 551007,536, 241720,75, 551012,4, 241735,7 ................................. ................................. ................................. SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(244241,1, 564379,01, 244242,76, 564376,3, 244259,59, 564386,54, 244251,37, 56 4400,14, 244245,54, 564396,48, 244251,6, 564385,54, 244241,1, 564379,01)) {7A42B22C-F30D-460C-8615-8B6083BBB3F7} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247852,537, 558231,679, 247850,79, 558231,19, 247855,48, 558215,5, 247862,903 , 558191,244, 247866,38, 558179,88, 247868,317, 558180,469, 247864,422, 558193,2 38, 247863,5, 558196,26, 247852,537, 558231,679)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {7A687E8B-B8EC-41D9-80CB-3EC2C2C44EF2} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. 375 rijen zijn geselecteerd. SQL> </tolorance1m> <tolorance1cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, .01)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance1cm-withoutcheck> <tolorance10cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEITERATE. ORA-13347: De co�rdinaten voor een boog mogen niet gelijk zijn. <tolorance10cm-withoutcheck> ------------------------------------------------------------------------------- It looks like you have some problematic geometries (which is often the case with spatial). The aggregate causes points to overlap. You could try a SMALLER tolerance, like 0.0005. Still, with the aggregate function, if two geometries have some overlap, they will be considered one, which could cause some strange effects. Another thing I have seen in the past is that flushing the SGA before executing your script may help. By the way: the error is a wrong translation. Call out is translated as toelichting, which is not correct. ODCIAGGREGATEITERATE is an external callout function. Too large values (in size) can cause problems like this. I have not seen workarounds for this. But there must be another way to join multiple geometries to one. Shakespeare
From: Shakespeare on 12 Jun 2008 12:53 "Eduard Witteveen" <eywitteveen(a)gmail.com> schreef in bericht news:f26766d3-a914-4f72-96d9-05226fc4bb5e(a)c58g2000hsc.googlegroups.com... On Jun 12, 3:04 pm, "Shakespeare" <what...(a)xs4all.nl> wrote: > ... > ORA-29913 may have something to do with a too small tolerance setting. Try > a > larger one and see what happens. Look at the end for output of the tolerance's > What OS are you on? The server is running on: cpu: 3.40 GHZ intel xeon(hyperthreaded) ram: 4032mb os: Windows 2003 SP servicepack: 2 <tolorance10cm> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 0.1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance10cm> <tolorance1m> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 -- check the input for the SDO_AGGR_UNION 18 AND TOPO.GEOMETRIE.SDO_GTYPE = 2003 19 AND SDO_GEOM.VALIDATE_GEOMETRY(GEOMETRIE, 1) = 'TRUE' 20 ) LINES 21 GROUP BY GUID; GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {001FA2FC-1448-42C5-BC5F-480AD7392891} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) {003E7376-7418-4361-A139-F58251E06127} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(252697,14, 556409,2, 252690,788, 556404,936, 252711,182, 556400,086, 252716,6 7, 556404,21, 252697,14, 556409,2)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {00708284-7AB4-4CCC-82D1-CE86BD38777F} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247689,96, 558009,63, 247722,7, 558019,81, 247722,152, 558021,61, 247689,42, 558011,33, 247689,96, 558009,63)) {00832FCD-A3D6-4422-BDE1-BE937C0740E8} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(241710,281, 551011,756, 241711,873, 551007,536, 241720,75, 551012,4, 241735,7 ................................. ................................. ................................. SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(244241,1, 564379,01, 244242,76, 564376,3, 244259,59, 564386,54, 244251,37, 56 4400,14, 244245,54, 564396,48, 244251,6, 564385,54, 244241,1, 564379,01)) {7A42B22C-F30D-460C-8615-8B6083BBB3F7} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(247852,537, 558231,679, 247850,79, 558231,19, 247855,48, 558215,5, 247862,903 , 558191,244, 247866,38, 558179,88, 247868,317, 558180,469, 247864,422, 558193,2 38, 247863,5, 558196,26, 247852,537, 558231,679)) GUID -------------------------------------- GEOMETRIE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- {7A687E8B-B8EC-41D9-80CB-3EC2C2C44EF2} SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR AY(-3,403E+38, 3,4028E+38, -3,403E+38, -3,403E+38, 3,4028E+38, -3,403E +38, 3,402 8E+38, 3,4028E+38, -3,403E+38, 3,4028E+38)) ERROR: ORA-03113: Einde-van-bestand op communicatiekanaal. 375 rijen zijn geselecteerd. SQL> </tolorance1m> <tolorance1cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, .01)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEMERGE. </tolorance1cm-withoutcheck> <tolorance10cm-withoutcheck> SQL> SELECT 2 DISTINCT(GUID), 3 SDO_AGGR_UNION(MDSYS.SDOAGGRTYPE(GEOMETRIE, 0.1)) AS GEOMETRIE 4 FROM 5 ( 6 SELECT 7 COALESCE(MULTI.PARENT, GUIDS.ID) AS GUID, 8 TOPO.GEOMETRIE AS GEOMETRIE 9 FROM DGDTW_TOPOGRAFIE TOPO 10 INNER JOIN DGDTW_OBJECTGUIDS GUIDS 11 ON GUIDS.DGDTW_PRIMARY_KEY = TOPO.ID 12 LEFT OUTER JOIN DGDTW_PARENT_CHILD MULTI 13 ON MULTI.CHILD = GUIDS.ID 14 AND ARCHIVE IS NULL 15 WHERE TOPO.VERVAL IS NULL 16 AND NOT TOPO.PARAMETERS = 515 17 ) LINES 18 GROUP BY GUID; SELECT * FOUT in regel 1: ..ORA-29913: Er is een fout opgetreden bij het uitvoeren van de toelichting ODCIAGGREGATEITERATE. ORA-13347: De co�rdinaten voor een boog mogen niet gelijk zijn. <tolorance10cm-withoutcheck> ----------------------------------------------------------------- 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
From: Eduard Witteveen on 16 Jun 2008 03:16 On Jun 12, 6:53 pm, "Shakespeare" <what...(a)xs4all.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! :D 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 )
From: Shakespeare on 17 Jun 2008 04:20
"Eduard Witteveen" <eywitteveen(a)gmail.com> schreef in bericht news:3ec08178-324b-4fd4-a584-7f23c267373d(a)l64g2000hse.googlegroups.com... > On Jun 12, 6:53 pm, "Shakespeare" <what...(a)xs4all.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! :D > > 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 ) Do you have a virus scanner running on your server or client? McAfee is well 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 |