Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: View in a procedure.

Re: View in a procedure.

From: <markp7832_at_my-deja.com>
Date: Mon, 20 Dec 1999 18:36:14 GMT
Message-ID: <83lsuu$9km$1@nnrp1.deja.com>


In article <83ksfs$plq$1_at_naxos.belnet.be>,   "Stéphane D'Hoedt" <sdhoedt_at_softhome.net> wrote:
> Hi,
>
> I wrote a view like this:
>
> CREATE OR REPLACE VIEW QREKSE ( REK_ID,
> R_SA_ID, R_SE_ID, SE_NAAM, R_NAAMSE,
> R_VOLGNUMMERSE, R_REFKLIENT, R_FAKNUMMER, R_REDEN,
> R_DATUMSCHULD, R_BEDRAGSCHULD, R_INPUTDATUM, HSSE,
> MIR, AMR, TIR, SCHADE,
> TOTEXCL, R_WN, SE_ID, R_REKOPGELOST,
> R_REKAFGEREKEND, SE_STRAAT, SE_HUISNR, SE_POSTNR,
> SE_GEMEENTE, SE_DOS, SE_AFRHSPERC, SE_AFRHS,
> SE_AFRCOM ) AS SELECT DISTINCT REKENING.REK_ID, REKENING.R_SA_ID,
> REKENING.R_SE_ID,
> SCHULDEISER.SE_NAAM, REKENING.R_NAAMSE,
REKENING.R_VOLGNUMMERSE,
> REKENING.R_REFKLIENT, REKENING.R_FAKNUMMER, REKENING.R_REDEN,
> REKENING.R_DATUMSCHULD,
> REKENING.R_BEDRAGSCHULD, REKENING.R_INPUTDATUM,
> (ROUND(R_BEDRAGSCHULD/SE_VERHOGINGSVPERC)+SE_VERHOGINGSV) AS
HSSE,
> ROUND(R_BEDRAGSCHULD*SE_INTREST) AS MIR,
> TRUNC(MONTHS_BETWEEN( NVL(R_REKOPGELOST,SYSDATE),
R_DATUMSCHULD)) AS
> AMR,
>
> OUND(R_BEDRAGSCHULD*SE_INTREST)*TRUNC(MONTHS_BETWEEN( NVL
(R_REKOPGELOST,SYSD
> ATE), R_DATUMSCHULD)) AS TIR,
> GREATEST(ROUND

(R_BEDRAGSCHULD*SE_SCHADELOOSSTELLING),SE_MINIMUM) AS
> SCHADE,
>
> (ROUND(R_BEDRAGSCHULD/SE_VERHOGINGSVPERC)+SE_VERHOGINGSV)+ROUND
(R_BEDRAGSCHU
> LD*SE_INTREST)*TRUNC(MONTHS_BETWEEN( NVL(R_REKOPGELOST,SYSDATE),
> R_DATUMSCHULD))+GREATEST(ROUND

(R_BEDRAGSCHULD*SE_SCHADELOOSSTELLING),SE_MINI
> MUM) AS TOTEXCL,
> REKENING.R_WN, SCHULDEISER.SE_ID, REKENING.R_REKOPGELOST,
> REKENING.R_REKAFGEREKEND,
> SCHULDEISER.SE_STRAAT, SCHULDEISER.SE_HUISNR,
SCHULDEISER.SE_POSTNR,
> SCHULDEISER.SE_GEMEENTE, SCHULDEISER.SE_DOS,
> SCHULDEISER.SE_AFRHSPERC,
> SCHULDEISER.SE_AFRHS, SCHULDEISER.SE_AFRCOM
> FROM SCHULDEISER, REKENING
> WHERE SCHULDEISER.SE_ID = REKENING.R_SE_ID AND
> (((REKENING.REK_ID) IS NOT NULL))
>
> And the view is to slow to run it. So I will write it in a procedure,
> because I know it is much faster to run it.
> But I have a problem. I have some problem to write the procedure. Can
> someone help me how to write this view in a procedure.
>
> Thanks,
> Stéphane D'Hoedt
>

To convert the view into a procedure you would convert the view into a cursor select statement. You would then open the cursor and could fetch each row returned. But this isn't going to save much since you are preforming the same query, and your calculations do not look complicated enough to really require removing from the SQL.

Have you verified that the query is being performed efficiently via an explain plan? Are the tables on different disks? Are the indexes on separate disks from the table, and if possible from each other?

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 20 1999 - 12:36:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US