Need help with monstrous query migrated from sql server

From: <skallagrimsson_at_gmail.com>
Date: 19 Apr 2006 01:32:59 -0700
Message-ID: <1145435579.315234.144240@t31g2000cwb.googlegroups.com>

Hi!

I need help with a query conversion from SQL Server to Oracle.

The SQL Server query runs pretty fast, but when converted to run in Oracle it performs poorly. Both versions included below.

Any ideas how to make the Oracle query better?

Thanks in advance!

SQL Server:

SELECT
   permnd, peraar, jurenhet, artskonto, 0 as sumbilagbel, akode,rkode,sum(hboksaldo) as sumhboksaldo,

   (select 1 --FROM avstemt a

            WHERE not exists (
                  select D.peraar
                  from Avstemt D
                  where D.permnd = A.permnd
                     AND D.peraar = A.peraar
                     AND upper(D.jurenhet) = upper(A.jurenhet)
                     AND D.artskonto = A.artskonto
                     AND (D.avstemtav is null OR D.avstemtav = '')))

   as avstemtav,
   (select ktonavn from Konto K where K.artskonto = A.artskonto) as kontonavn,

   COUNT(*) as antall,
   0 as qaantall,
   COUNT(*) as avstemtantall
   FROM Avstemt A

      WHERE upper(jurenhet) = upper('sy')
         AND permnd = 'jul'
         AND peraar = 2004
         AND (
            NOT EXISTS (
               SELECT 1
                  FROM Bilag B
                  WHERE A.permnd = B.permnd
                     AND A.peraar = B.peraar
                     AND upper(A.jurenhet) = upper(B.jurenhet)
                     AND A.artskonto = B.artskonto
                     AND B.deleted = 'n'
                     AND upper(A.rkode) = upper(B.rkode)
                     AND upper(A.akode) = upper(B.akode)
                  )
         )

GROUP BY permnd, peraar, jurenhet, artskonto, rkode, akode HAVING SUM(hboksaldo) <> 0

ORACLE: /* @(#)C:\AdventNet\SwisSQL\SQLServerToOracle3.0/temp2_PL.sql - Generated by AdventNet SwisSQL (SQLServer To oracle) Conversion tool.*/ ;
SELECT

		 permnd,
		 peraar,
		 jurenhet,
		 artskonto,
		 0 as sumbilagbel,
		 akode,
		 rkode,
		 SUM(hboksaldo) as sumhboksaldo,

(SELECT 1

FROM SYS.DUAL

WHERE	 NOT   exists
	(SELECT D.peraar
	FROM  Avstemt D
	WHERE	 D.permnd  = A.permnd
	 AND	D.peraar  = A.peraar
	 AND	UPPER(D.jurenhet)  = UPPER(A.jurenhet)
	 AND	D.artskonto  = A.artskonto
	 AND	(D.avstemtav  is null
	 OR	D.avstemtav  = '')))

 as avstemtav,
(SELECT ktonavn

FROM Konto K
WHERE K.artskonto = A.artskonto)
 as kontonavn,

		 COUNT(*) as antall,
		 0 as qaantall,
		 COUNT(*) as avstemtantall
FROM  Avstemt A
WHERE	 UPPER(jurenhet)  = UPPER('sy')
 AND	permnd  = 'jul'
 AND	peraar  = 2004
 AND	(NOT   EXISTS
	(SELECT 1
	FROM  Bilag B
	WHERE	 A.permnd  = B.permnd
	 AND	A.peraar  = B.peraar
	 AND	UPPER(A.jurenhet)  = UPPER(B.jurenhet)
	 AND	A.artskonto  = B.artskonto
	 AND	B.deleted  = 'n'
	 AND	UPPER(A.rkode)  = UPPER(B.rkode)
	 AND	UPPER(A.akode)  = UPPER(B.akode) ))
GROUP BY permnd,
	 peraar,
	 jurenhet,
	 artskonto,
	 rkode,
	  akode 

HAVING SUM(hboksaldo) <> 0

/ Received on Wed Apr 19 2006 - 03:32:59 CDT