Take a look at the windowing functions under 'ana;ytic
functions' in the data warehouse guide...They are very
very cool..
hth
connor
- Jack van Zanen <nlzanen1_at_EY.NL> wrote: > Maybe
having a bad hairday but following code will
> give me for every period
> number the sum of all work in progress for the
> period plus the previous 12
> periods. Basically it takes the period number from
> the current record and
> sums a column of that record with the previous 12
> periods.
>
> year period value
> 2000 1 1
> 2000 2 2
> 2000 3 3
> 2000 4 4
> 2000 5 5
> 2000 6 6
> 2000 7 7
> 2000 8 8
> 2000 9 9
> 2000 10 10
> 2000 11 11
> 2000 12 12
> 2001 1 13
> 2001 2 14
> 2001 3 15
>
> So for period 2001 1 I need the values 1 thru 13
> added together (91)
> Period 2001 2 needs to be the values 2 thru 14 added
> together (104)
> etc....
>
> As you maybe can imagine the explain plan for this
> baby is 4 full
> tablescans on quite large tables. (see below)
>
> My question is if somebody has a better solution to
> handle this query?????
>
> Oracle 8.0.5 !!!
> AIX 4.3.3
>
>
> SELECT V1.ENGAGEMENT
> , V1.YEAR
> , V1.PERIOD
> , MAX(V1.NET_FEE_EARNED_PTD)
> , MAX(V1.EXP_WIP_VAL_PTD)
> , SUM(V2.TIME_WIP_VAL)
> , MAX(V1.GNRL_WON)
> , MAX(V1.GNRL_WOFF)
> , MAX(V1.TIME_WOFF)
> , MAX(V1.WIP_PROV)
> , MAX(V1.EXP_WOFF)
> , MAX(V1.DB_WOFF)
> , MAX(V1.DB_PROV)
> FROM
> (
> select max(WSTAT.ENG_NUMB) AS ENGAGEMENT,
> max(WSTAT.FNANCL_YEAR) AS YEAR,
> max(WSTAT.PRD_NUMB) AS PERIOD,
> SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> - SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> 0))
> - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> 0))
> - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> NET_FEE_EARNED_PTD,
> SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0))
> EXP_WIP_VAL_PTD,
> SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> TIME_WIP_VAL,
> SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> GNRL_WON,
> SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> GNRL_WOFF,
> SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> 0)) TIME_WOFF,
> SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> WIP_PROV,
> SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, 0))
> EXP_WOFF,
> SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> DB_WOFF,
> SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> DB_PROV
> FROM ENG_WIP_STATS WSTAT,
> ENG_DBTRS_STATS DSTAT
> WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB
> AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR
> AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB
> GROUP BY WSTAT.ENG_NUMB,
> WSTAT.FNANCL_YEAR,
> WSTAT.PRD_NUMB
> ) v1,
> (
> select max(WSTAT.ENG_NUMB) AS ENGAGEMENT,
> max(WSTAT.FNANCL_YEAR) AS YEAR,
> max(WSTAT.PRD_NUMB) AS PERIOD,
> SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> - SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> 0))
> - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD,
> 0))
> - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> NET_FEE_EARNED_PTD,
> SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0))
> EXP_WIP_VAL_PTD,
> SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0))
> TIME_WIP_VAL,
> SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0))
> GNRL_WON,
> SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0))
> GNRL_WOFF,
> SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD,
> 0)) TIME_WOFF,
> SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0))
> WIP_PROV,
> SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, 0))
> EXP_WOFF,
> SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0))
> DB_WOFF,
> SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0))
> DB_PROV
> FROM ENG_WIP_STATS WSTAT,
> ENG_DBTRS_STATS DSTAT
> WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB
> AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR
> AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB
> GROUP BY WSTAT.ENG_NUMB,
> WSTAT.FNANCL_YEAR,
> WSTAT.PRD_NUMB
> ) V2
> WHERE v1.ENGAGEMENT=V2.ENGAGEMENT(+)
> AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM') >=
> ADD_MONTHS(TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM'),-12)
> AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM') <=
> TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM')
> GROUP BY V1.ENGAGEMENT
> , V1.YEAR
> , V1.PERIOD
>
>
>
> Explain plan:
>
> ID PID QUERY_PLAN
> ----- -----
>
> 0 SELECT STATEMENT Cost = 12531921
> 1 0 SORT GROUP BY Card=62754915
> 2 1 MERGE JOIN OUTER Card=62754915
> 3 2 SORT JOIN Card=1584360
> 4 3 VIEW Card=1584360
> 5 4 SORT GROUP BY Card=1584360
> 6 5 FILTER Card=
> 7 6 MERGE JOIN OUTER Card=
> 8 7 SORT JOIN Card=1354977
> 9 8 TABLE ACCESS FULL
> ENG_DBTRS_STATS
> Card=1354977
> 10 7 SORT JOIN Card=1956365
> 11 10 TABLE ACCESS FULL
> ENG_WIP_STATS Card=1956365
> 12 2 FILTER Card=
> 13 12 SORT JOIN Card=
> 14 13 VIEW Card=1584360
> 15 14 SORT GROUP BY Card=1584360
> 16 15 FILTER Card=
> 17 16 MERGE JOIN OUTER Card=
> 18 17 SORT JOIN
> Card=1354977
> 19 18 TABLE ACCESS FULL
> ENG_DBTRS_STATS
> Card=1354977
> 20 17 SORT JOIN
> Card=1956365
> 21 20 TABLE ACCESS FULL
> ENG_WIP_STATS
> Card=1956365
>
>
> THX
>
>
> De informatie verzonden in dit e-mailbericht is
> vertrouwelijk en is
> uitsluitend bestemd voor de geadresseerde.
> Openbaarmaking,
> vermenigvuldiging, verspreiding en/of verstrekking
> van deze informatie aan
> derden is, behoudens voorafgaande schriftelijke
> toestemming van Ernst &
> Young, niet toegestaan. Ernst & Young staat niet in
> voor de juiste en
> volledige overbrenging van de inhoud van een
> verzonden e-mailbericht, noch
> voor tijdige ontvangst daarvan. Ernst & Young kan
> niet garanderen dat een
> verzonden e-mailbericht vrij is van virussen, noch
> dat e-mailberichten
> worden overgebracht zonder inbreuk of tussenkomst
> van onbevoegde derden.
>
> Indien bovenstaand e-mailbericht niet aan u is
> gericht, verzoeken wij u
> vriendelijk doch dringend het e-mailbericht te
> retourneren aan de verzender
> en het origineel en eventuele kopieën te verwijderen
> en te vernietigen.
>
> Ernst & Young hanteert bij de uitoefening van haar
> werkzaamheden algemene
> voorwaarden, waarin een beperking van
> aansprakelijkheid is opgenomen. De
> algemene voorwaarden worden u op verzoek kosteloos
> toegezonden.
>
> The information contained in this communication is
> confidential and is
> intended solely for the use of the individual or
> entity to whom it is
> addressed. You should not copy, disclose or
> distribute this communication
> without the authority of Ernst & Young. Ernst &
> Young is neither liable for
> the proper and complete transmission of the
> information contained in this
> communication nor for any delay in its receipt.
> Ernst & Young does not
> guarantee that the integrity of this communication
> has been maintained nor
> that the communication is free of viruses,
> interceptions or interference.
>
> If you are not the intended recipient of this
> communication please return
> the communication to the sender and delete and
> destroy all copies.
>
> In carrying out its engagements, Ernst & Young
> applies general terms and
> conditions, which contain a clause that limits its
> liability. A copy of
> these terms and conditions is available on request
> free of charge.
>
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jack van Zanen
> INET: nlzanen1_at_EY.NL
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Aug 15 2002 - 09:28:38 CDT