Re: Cursors ...

From: Meinhard Schnoor-Matriciani <Meinhard_at_NewYorker.de>
Date: Tue, 7 May 2002 10:23:02 +0200
Message-ID: <1020759807.851553@beavis>

Hi Sybrand,

thx for the answer and sorry for being unprecise. I nailed the problem down to the following two functions.

function GetKalendertageFaktor( pFilialePK in filiale.filialepk%Type ,

                                                   pLandisoid in
land.landisoid%Type,
                                                   pBundeslandid in
bundesland.bundeslandid%Type,
                                                   pVON in
periodeabschnitt.PERIODEABSCHNITTVON%Type,
                                                   pBIS in
periodeabschnitt.PERIODEABSCHNITTBIS%Type
                              ) return float as

v_Faktor float;

cursor c_Kalender is
select sum(nvl(f1,nvl(f2,nvl(f3,f0)))) tage from (

        select k1.KALENDERTAGPK,
               k1.kalendertag kt,
               k1.kalendertagfaktor F0,

GetKalenderTagFaktor(pFilialePK,pLandisoID,pBundeslandID,k1.kalendertag) F1,

GetKalenderTagFaktor(NULL,pLandisoID,pBundeslandID,k1.kalendertag) F2,

               GetKalenderTagFaktor(NULL,pLandisoID,NULL,k1.kalendertag)  F3
        from  kalendertag k1
        where k1.bundeslandid is null
          and k1.landisoid is null
          and k1.filialepk is null
          and k1.kalendertag>=pVon
          and k1.kalendertag<=pBis

    );

begin

    open c_Kalender;
    fetch c_Kalender into v_faktor;
    close c_Kalender;
    return (v_Faktor);
end;

function GetKalendertagFaktor( pFilialePK in filiale.filialepk%Type ,

                                                 pLandisoid in
land.landisoid%Type,
                                                 pBundeslandid in
bundesland.bundeslandid%Type,
                                                 pTag in

periodeabschnitt.PERIODEABSCHNITTVON%Type ) return float as

v_Faktor float;

cursor c_Kalender is

       select k2.kalendertagfaktor
        from  kalendertag k2
        where k2.bundeslandid=pBundeslandid
          and k2.landisoid=pLandisoid
          and k2.filialepk=pFilialePK
          and k2.kalendertag=pTag;

begin

    open c_Kalender;
    fetch c_Kalender into v_faktor;
    close c_Kalender;
    return (v_Faktor);
end;

Whe I take the first select statement and execute it via TOAD it takes
>=500ms. If I modify the statement and exchange the call to the function
GetKalendertagFaktor with the corresponding subselects it takes only 20ms.

Regards
Meinhard Received on Tue May 07 2002 - 03:23:02 CDT