Re: Oracle Spatial SDO Aggregate Union fails with ORA-29913 ODCIAGGREGATEMERGE

"Eduard Witteveen" <eywitteveen_at_gmail.com> schreef in bericht news:3ec08178-324b-4fd4-a584-7f23c267373d_at_l64g2000hse.googlegroups.com...
> On Jun 12, 6:53 pm, "Shakespeare" <what..._at_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 Received on Tue Jun 17 2008 - 03:20:17 CDT