"Eduard Witteveen" <eywitteveen@[EMAIL PROTECTED]
> schreef in bericht
news:3ec08178-324b-4fd4-a584-7f23c267373d@[EMAIL PROTECTED]
> On Jun 12, 6:53 pm, "Shakespeare" <what...@[EMAIL PROTECTED]
> 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 Cor****ation. 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
|
