[SQL] waarden optellen uit verschillende regels

[107mb]

ik heb een tabel met 14 velden:
kostenplaats, jaar, omzet_jan, omzet_feb, omzet_mrt ... , omzet_dec

nu heb ik een SQL statement nodig die het volgende resultaat genereert:
veld 1: kostenplaats
veld 2: som omzet januari 2010 t/m omzet september 2010 + som omzet oktober 2009 t/m omzet december 2009.

dit veld 2 berekent dus een estimate van het hele jaar.

met een union lukt mij dit niet, dan krijg ik
kostenplaats x, 2009, bedrag 1
kostenplaats x, 2010, bedrag 2
kostenplaats y, 2009, bedrag 3
kostenplaats y, 2010, bedrag 4

een met een case when krijg ik ook niet voor elkaar?

heeft iemand de oplossing?

[curry684]

Ja, normaliseren.

[RobIII]

[code=sql]
select kostenplaats, sum(omzet_jan + omzet_feb + omzet_mrt ... omzet_sept), sum(omzet_okt, ... omzet_dec)
from ...
group by kostenplaats
[/code]
Lijkt me nou niet bepaald rocket science
Oh wacht; je vraag was jan/sept 2010 en okt/dec 2009 In dat geval zou een genormaliseerde tabel sowieso al vele malen beter zijn.

Met een case zou 't moeten kunnen:

SQL:

select kostenplaats, sum(case when jaar = 2009 then omzet_okt + ... + omzet_dec when jaar = 2010 then omzet_jan + .... + omzet_sept end) 
from...

Verder: met curry684: Normaliseer die hap eerst eens (even aangenomen dat dit niet met opzet zo gedenormaliseerd is).

[ Voor 183% gewijzigd door RobIII op 18-10-2010 14:50 . Reden: Must... get... coffee... ]

[curry684]

Laat ik dan iig maar vast het goeie antwoord voorzeggen voordat je Rob3 te serieus gaat nemen op deze vroege maandag

SQL:

SELECT kostenplaats, sum(omzet)
FROM omzet
WHERE (year = 2009 and month between 10 and 12)
   OR (year = 2010 and month between 1 and 9)
GROUP BY kostenplaats

[ Voor 6% gewijzigd door curry684 op 18-10-2010 13:59 . Reden: je wilde kostenplaats ook ]

[RobIII]

curry684 schreef op maandag 18 oktober 2010 @ 13:56:
Laat ik dan iig maar vast het goeie antwoord voorzeggen voordat je Rob3 te serieus gaat nemen op deze vroege maandag

SQL:

SELECT sum(omzet)
FROM omzet
WHERE (year = 2009 and month between 10 and 12)
   OR (year = 2010 and month between 1 and 9)

Voor de genormaliseerde vorm wel ja

[curry684]

Ja tuurlijk, ik geef geen hapklare slechte oplossingen zoals jij he

[Motrax]

Kan iemand me uitleggen waarom hier genormaliseerd zou moeten worden? Het lijkt er op dat de query direct wordt losgelaten op een transactie tabel, waarbij geen onderverdeling is gemaakt naar tijdsperiode. Helemaal zo gek nog niet aangezien een transactioneel systeem geen moer te maken heeft in welke tijdsperiode een bepaalde transactie heeft plaatsgevonden, alleen de registratie van wanneer de transactie plaatsvond is van belang.

Vervolgens wil je sommeren op bepaalde tijdsperiodes en dan pas wordt het relevant om je data te groeperen.

Als je dat vaak doet, dan is het wel interessant op een datum tabel op te nemen waarbij elke datum gekoppeld is aan een aantal velden zodat het joinen/groeperen een stuk makkelijker gaat. Dus zoiets als:
Datum || Jaar || Maand || Dag || JaarMaand|| JaarMaandDag || Kwartaal || JaarKwartaal || Helft || JaarHelft || etc.

Maar ook hierbij geldt dat je niet je transactionele systeem wil normaliseren. Een transactierecord koppel je op basis van de datum aan de tijdtabel, waarbij de datum als smartkey wordt gebruikt en niet een gegeneerde sleutel. Dus niet een extra referentie in je transactieregel opnemen naar de datumtabel.

[107mb]

helaas gaat dit niet zo werken,

de tabel ziet er exact zo uit:
kostenplaats
jaar
wtg1 (omzet januari)
wtg2 (omzet februari)
wtg3 (omzet maart)
...
enz.

er valt dus m.i. niet te normaliseren aangezien er geen redundantie is.
ik had ook liever een andere opbouw van de db gezien, maar ik moet er mee werken.


ik kan dus geen sum() omdat de velden een andere naam hebben.

ik heb zelf gedacht aan

SQL:

select kostenplaats, 
CASE WHEN jaar = 2009 THEN [WTG10] + [WTG11] + [WTG12] end 
+ CASE WHEN jaar = 2010 THEN [WTG1] + [WTG2] + [WTG3] end as omzet2010

ik krijg echter alleen NULL bij het berekende veld

[curry684]

Ik snap geen hol van je verhaal Motrax maar het lijkt erop dat je OLAP en OLTP enorm door de war aan het halen bent en weinig tot geen real world ervaring met databases hebt?

107mb schreef op maandag 18 oktober 2010 @ 14:14:
wtg1 (omzet januari)
wtg2 (omzet februari)
wtg3 (omzet maart)
...

er valt dus m.i. niet te normaliseren aangezien er geen redundantie is.

Genummerde velden zijn per definitie een overtreding van de eerste normaalvorm hoor....

107mb schreef op maandag 18 oktober 2010 @ 14:14:
ik heb zelf gedacht aan

SQL:

select kostenplaats, 
CASE WHEN jaar = 2009 THEN [WTG10] + [WTG11] + [WTG12] end 
+ CASE WHEN jaar = 2010 THEN [WTG1] + [WTG2] + [WTG3] end as omzet2010

Je mist het basisbeginsel dat SQL een set-based query language is. Denk eens set-based, dus in tables en resultsets, dan snap je direct waarom dit niet kan werken.

[ Voor 79% gewijzigd door curry684 op 18-10-2010 14:19 ]

[107mb]

je bedoelt dus dat het volgende een betere opzet is:
kostenplaats, jaar, maand, bedrag


ik zit helaas aan de huidige opzet van deze tabel vast...

[ Voor 7% gewijzigd door 107mb op 18-10-2010 14:19 ]

[NMe]

107mb schreef op maandag 18 oktober 2010 @ 14:14:
er valt dus m.i. niet te normaliseren aangezien er geen redundantie is.

Er valt wél te normaliseren: je zou gewoon de transacties moeten opslaan en elk een eigen rij geven, die je weer netjes kunt optellen met SUM (of zelfs gedenormaliseerd/redundant kunt opslaan). Eventueel zou je zelfs een rij kunnen aanmaken in een tabel per maand, in plaats van een rij per jaar met daarin een veld per maand.

ik had ook liever een andere opbouw van de db gezien, maar ik moet er mee werken.

Kun/mag je het aanpassen of niet? Zo ja: vooral wél doen. Zo nee, jammer, maar komt inderdaad voor in het bedrijfsleven. In dat geval:

ik heb zelf gedacht aan

SQL:

select kostenplaats, 
CASE WHEN jaar = 2009 THEN [WTG10] + [WTG11] + [WTG12] end 
+ CASE WHEN jaar = 2010 THEN [WTG1] + [WTG2] + [WTG3] end as omzet2010

ik krijg echter alleen NULL bij het berekende veld

Natuurlijk, als een jaar nog niet helemaal vol is heeft tenminste één van die velden een nullwaarde als uitkomst. Null + 1 = null, niet 1. Als één van de velden dus null is, dan is je uitkomst ook null. Je zou IsNull() of Coalesce() kunnen gebruiken om zeker te zijn dat je geen null-waarde optelt.

Overigens werkt daarmee nog steeds je query niet, zoals Curry al aangeeft.

[curry684]

107mb schreef op maandag 18 oktober 2010 @ 14:19:
je bedoelt dus dat het volgende een betere opzet is:
kostenplaats, jaar, maand, bedrag

Dan schiet je ineens van 0NV naar 3NV ja, 3 normaalvormen in 1 klap

_{Ga ik er even gevoeglijk van uit dat kostenplaats een foreign key is naar een losse tabel met kostenplaatsen maar dat gaat wat buiten deze discussie...}

ik zit helaas aan deze tabel vast...

En waarom zou je expres op een expliciet honderd procent fout opgezette database moeten blijven werken? "Omdat de baas dat zegt" is geen geldig antwoord.

[ Voor 14% gewijzigd door curry684 op 18-10-2010 14:21 ]

[107mb]

ik moet mij maar eens gaan verdiepen in normaliseren. Ik kan e.e.a. nog niet volgen...

OT:

ik zit er aan vast omdat deze tabel beladen wordt vanuit SAP
vervolgens maken vele excel applicaties gebruik van deze tabel (en overige tabellen).

er moet dus veel gewijzigd worden (SAP interface, die ik door iemand moet laten aanpassen) alsmede de query's in de excel-werkbladen en macro's.
De excel bladen worden dagelijks door vele collega's geraadpleegd. Tevens ben ik zelf erg druk met diverse projecten...

conclusie: Voorlopig blijft alles bij het oude...

[Motrax]

curry684 schreef op maandag 18 oktober 2010 @ 14:14:
Ik snap geen hol van je verhaal Motrax maar het lijkt erop dat je OLAP en OLTP enorm door de war aan het halen bent en weinig tot geen real world ervaring met databases hebt?

*gniffel* Ik moest wel om je reactie lachen. Maar goed, wat voor mij duidelijk is en voor jou niet, betekent gewoon dat ik iets niet goed overbreng.

Ik heb ervaring met OLAP, OLTP, real-time rapportage op OLTP, rapportage op OLAP, ODS, noem het maar op.

Een poging om het duidelijker te krijgen:
Wat ik eigenlijk bedoelde is dat ik vermoedde dat de TS een query stuurt naar een transactionele tabel in een OLTP systeem. Een typisch OLTP systeem kent geen tijdsdimensie en zal je dus met case statements moeten werken. Een alternatief om de query performance te verbeteren is en wanneer je vaker op bepaalde tijdsperiodes moet groeperen is om in het OLTP systeem een tijdsdimensie op te nemen, met als key de datum, bijvoorbeeld yyyymmdd (integer) of als yyyy-mm-dd (date).

Maar goed, de tussentijdse reactie van de TS laat zien dat er wel een normalisatie 'probleempje' is

NMe schreef op maandag 18 oktober 2010 @ 14:19:
Natuurlijk, als een jaar nog niet helemaal vol is heeft tenminste één van die velden een nullwaarde als uitkomst. Null + 1 = null, niet 1. Als één van de velden dus null is, dan is je uitkomst ook null. Je zou IsNull() of Coalesce() kunnen gebruiken om zeker te zijn dat je geen null-waarde optelt.

Overigens werkt daarmee nog steeds je query niet, zoals Curry al aangeeft.

Coalescence geeft alleen de eerste niet null waarde uit een resultaat set, niet zo handig in dit geval. Ik zou eerder met een nvl() alles omzetten naar 0.

107mb schreef op maandag 18 oktober 2010 @ 14:32:
ik moet mij maar eens gaan verdiepen in normaliseren. Ik kan e.e.a. nog niet volgen...

OT:

ik zit er aan vast omdat deze tabel beladen wordt vanuit SAP
vervolgens maken vele excel applicaties gebruik van deze tabel (en overige tabellen).

er moet dus veel gewijzigd worden (SAP interface, die ik door iemand moet laten aanpassen) alsmede de query's in de excel-werkbladen en macro's.
De excel bladen worden dagelijks door vele collega's geraadpleegd. Tevens ben ik zelf erg druk met diverse projecten...

conclusie: Voorlopig blijft alles bij het oude...

De tabelattributen klonken mij als SAP BW in de oren. Het lijkt er op dat je direct data uit een info cube krijgt. Mocht je ooit er aan toe komen om de interface aan te laten passen, vraag er dan om de data te sorteren per kostenplaats, per jaar (0CALYEAR) en per maand (0CALMONTH).

[curry684]

Nou kijk dan heb je iig nu je geldige excuus voor de kut-DB 'Legacytroep' is helaas acceptabel.

Voor je query zou ik stiekem gewoon een ranzige join gebruiken als de DB toch kut is:

SQL:

SELECT kostenplaats, tbl1.wtg1 + ... + tbl1.wtg9 + tbl2.wtg10 + tbl2.wtg11 + tbl2.wtg12 as omzet
FROM omzet AS tbl1
JOIN omzet AS tbl2 ON tbl1.jaar = 2010 and tbl2.jaar = 2009

[curry684]

Motrax schreef op maandag 18 oktober 2010 @ 14:42:
[...]

*gniffel* Ik moest wel om je reactie lachen. Maar goed, wat voor mij duidelijk is en voor jou niet, betekent gewoon dat ik iets niet goed overbreng.

Nou niet helemaal, ik kan gewoon geen enkele context bedenken waarin "Maar ook hierbij geldt dat je niet je transactionele systeem wil normaliseren" een acceptabele uitspraak is Normalisatie is juist nergens zo belangrijk als in OLTP

edit:
nonde Rob je had hiertussen gepost, ik had geen dubbelpost

[ Voor 7% gewijzigd door curry684 op 18-10-2010 14:49 ]

[RobIII]

curry684 schreef op maandag 18 oktober 2010 @ 14:48:

edit:
nonde Rob je had hiertussen gepost, ik had geen dubbelpost

My bad; ik heb een tekort aan koffie vandaag

Anyway: Los van de (eventuele) null values en de group by die je zelf nog mag verzinnen staat de oplossing gewoon in dit topic hoor

[ Voor 20% gewijzigd door RobIII op 18-10-2010 15:03 ]

[Motrax]

Ik kan wel een context verzinnen

Ik ben het wel eens met je dat een transactioneel systeem genormaliseerd móet worden, maar als gaat om normalisatie van je transactioneel systeem voor rapportage behoeften (verschillende tijdsperiodes), dan moet je afvragen of dat wel de juiste omgeving is om te normaliseren. Als je dat al wilt doen, dan is een losse tijdsdimensietabel in het transactionele systeem een oplossing.

Even voor de duidelijkheid, normalisatie voor rapportage behoefte zou ik zoiets willen in een OLTP:
Verkoopregelid
Verkoperid
Klantid
Datum
Productid
Aantal
Geld
(waarbij dus bij verkoop maar 1 verkoper, 1 klant, 1 product en 1 datum betrokken is, lekker simpel )

Dan een tijdsdimensie:
Datum
Jaar
Kwartaal etc.

Dan kan ik joinen tussen de transactieregels en de tijdsdimensie en daarmee relatief snel groeperen op bepaalde tijdsperiodes.

Ik zou in een transactioneel systeem niet graag verder willen normaliseren naar jaar, kwartaal en andere tijdsperiodes die een key hebben in de transactietabel. 't heeft namelijk weinig met de transactie zelf te maken, maar met rapportage.

Maar goed, ik denk dat we het zo wel met elkaar eens zijn?

[Janoz]

@Mortax:

Even reageren op je

Het lijkt er op dat de query direct wordt losgelaten op een transactie tabel, waarbij geen onderverdeling is gemaakt naar tijdsperiode.

Ik zie toch echt een kolom jaar staan (en vervolgens een kolom per maand). Er is dus wel degelijk een onderverdeling in tijd, alleen hebben ze jaar als resolutie genomen. Dit gaat inderdaad jammerlijk falen aangezien de TS zijn gegevens in maanden terug wil.

[NMe]

Motrax schreef op maandag 18 oktober 2010 @ 14:42:
[...]

Coalescence geeft alleen de eerste niet null waarde uit een resultaat set, niet zo handig in dit geval. Ik zou eerder met een nvl() alles omzetten naar 0.

Nvl heet in T-SQL IsNull, en die haal ik gewoon aan. Verder doen Coalesce(null, 0), nvl(null, 0) en IsNull(null, 0) alledrie precies hetzelfde in deze context.

[JaQ]

107mb schreef op maandag 18 oktober 2010 @ 13:49:
heeft iemand de oplossing?

Subqueries?

SQL:

SELECT a.kostenplaats,
       a.som + b.som as omzet
  from (select kostenplaats, sum(omzet_jan + omzet_feb + omzet_mrt )
         from tabel
        where jaar = 2010
        group by kostenplaats ) a
,      (select kostenplaats, sum(omzet_okt + omzet_nov +omzet_dec )
         from tabel
        where jaar = 2009
        group by kostenplaats ) b
 where a.kostenplaats = b.kostenplaats

Daarna wel excuses aanbieden aan je DBA voor je ranzige query

[curry684]

Dan is curry684 in "[SQL] waarden optellen uit verschillende..." toch stukkie simpeler en vriendelijker naar de DBA.... alleen was ik vergeten de kostenplaatsen te koppelen, maar het mag nog wel een uitdaging blijven he

[JaQ]

curry684 schreef op maandag 18 oktober 2010 @ 23:19:
Dan is curry684 in "[SQL] waarden optellen uit verschillende..." toch stukkie simpeler en vriendelijker naar de DBA.... alleen was ik vergeten de kostenplaatsen te koppelen, maar het mag nog wel een uitdaging blijven he

Je bent aardig stellig.... Puur ter vermaak loop ik je statement na (stukkie simpeler en vriendelijker naar de DBA)

Simpeler ligt er maar net aan waar je aan gewent bent. Persoonlijk vind ik ANSI-queries helemaal niet simpeler dan Oracle-dialect queries, dus voor mij is jouw query lastig (ergo: simpeler is niet meetbaar in deze).

Om je tweede uitspraak te controleren (vriendelijker naar de DBA) op juistheid heb ik een testje gemaakt in Oracle 11.2.0.2 op 64-bit Linux (draaiend in een VM op mijn laptop). Ik heb de gegevens die nu bekend zijn over de tabel verwerkt, een aantal kostenplaatsen en een paar jaar historie. Er zijn geen indexen aangemaakt, want er is op dit moment niets bekend over indexen of sleutels.

De test wordt tweemaal gedraaid: éénmaal met de data in cache en eenmaal zonder de data in cache. Ik denk dat het script verder voor zichzelf spreekt?

SQL:

set echo on

conn/as sysdba

drop user demo cascade
/

create user demo identified by demo
  default tablespace users
  temporary tablespace temp
  quota unlimited on users
/

/* Basic privs */
grant connect, resource to demo;
/* for trace */
grant alter session to demo;
/* for flush of shared_pool */
grant alter system to demo;


connect demo/demo

/* The table */
create table omzet
( jaar         number(4)  not null
, kostenplaats number(6)  not null
, wtg1         number(10) default 10
, wtg2         number(10) default 10
, wtg3         number(10) default 10
, wtg4         number(10) default 10
, wtg5         number(10) default 10
, wtg6         number(10) default 10
, wtg7         number(10) default 10
, wtg8         number(10) default 10
, wtg9         number(10) default 10
, wtg10        number(10) default 10
, wtg11        number(10) default 10
, wtg12        number(10) default 10
)
/

/* Some data */
create sequence kostenplaats_seq
  start with 1
/

begin
  for i in 1..99999 loop
    insert into omzet (jaar,kostenplaats) values (2010, kostenplaats_seq.nextval);
  end loop;
  insert into omzet (jaar, kostenplaats) ( select 2009, kostenplaats from omzet where jaar = 2010);
  insert into omzet (jaar, kostenplaats) ( select 2008, kostenplaats from omzet where jaar = 2010);
  insert into omzet (jaar, kostenplaats) ( select 2007, kostenplaats from omzet where jaar = 2010);
  insert into omzet (jaar, kostenplaats) ( select 2006, kostenplaats from omzet where jaar = 2010);
  insert into omzet (jaar, kostenplaats) ( select 2005, kostenplaats from omzet where jaar = 2010);
end;
/

/* statistics */
exec dbms_stats.gather_table_stats(ownname => 'DEMO', tabname => 'OMZET', cascade => TRUE);

/* First trace, without cache */
connect demo/demo

set termout off

alter session set sql_trace=true
/

/* clear caches */
alter system flush shared_pool;
alter system flush buffer_cache;

/* Curry */
select t1.kostenplaats, sum(t1.wtg1 + t1.wtg2 + t1.wtg3 + t1.wtg4 + t1.wtg5 + t1.wtg6 + t1.wtg7 + t1.wtg8 + t1.wtg9 + t2.wtg10 + t2.wtg11 + t2.wtg12)
  from omzet t1
  join omzet t2 ON t1.kostenplaats = t2.kostenplaats and t1.jaar=2010 and t2.jaar=2009
 group by t1.kostenplaats
/

/* Clear caches */
alter system flush shared_pool;
alter system flush buffer_cache;

/* JaQ */
select t1.kostenplaats,
       t1.som + t2.som
  from ( select kostenplaats, sum(wtg1 + wtg2 + wtg3 + wtg4 + wtg5 + wtg6 + wtg7 + wtg8 + wtg9) as som
           from omzet t1
          where jaar = 2010
          group by kostenplaats ) t1,
       ( select kostenplaats, sum(wtg10 + wtg11 + wtg12) as som
           from omzet t1
          where jaar = 2009
          group by kostenplaats ) t2
 where t1.kostenplaats = t2.kostenplaats
/

alter system set sql_trace=false
/

/* Second trace, with cache */
connect demo/demo

set termout off

alter system set sql_trace=true
/

/* Clear caches */
alter system flush shared_pool;
alter system flush buffer_cache;

/* load cache */
select *
  from omzet
/

/* Curry */
select t1.kostenplaats, sum(t1.wtg1 + t1.wtg2 + t1.wtg3 + t1.wtg4 + t1.wtg5 + t1.wtg6 + t1.wtg7 + t1.wtg8 + t1.wtg9 + t2.wtg10 + t2.wtg11 + t2.wtg12)
  from omzet t1
  join omzet t2 ON t1.kostenplaats = t2.kostenplaats and t1.jaar=2010 and t2.jaar=2009
 group by t1.kostenplaats
/

/* JaQ */
select t1.kostenplaats,
       t1.som + t2.som
  from ( select kostenplaats, sum(wtg1 + wtg2 + wtg3 + wtg4 + wtg5 + wtg6 + wtg7 + wtg8 + wtg9) as som
           from omzet t1
          where jaar = 2010
          group by kostenplaats ) t1,
       ( select kostenplaats, sum(wtg10 + wtg11 + wtg12) as som
           from omzet t1
          where jaar = 2009
          group by kostenplaats ) t2
 where t1.kostenplaats = t2.kostenplaats
/

alter session set sql_trace = false
/

set termout on

Nu de resultaten *insert tromgeroffel*

zonder cache:

code:

select t1.kostenplaats, sum(t1.wtg1 + t1.wtg2 + t1.wtg3 + t1.wtg4 + t1.wtg5 + t1.wtg6 + t1.wtg7 + t1.wtg8 + t1.wtg9 + t2.wtg10 + t2.wtg11 + t2.wtg12)
  from omzet t1
  join omzet t2 ON t1.kostenplaats = t2.kostenplaats and t1.jaar=2010 and t2.jaar=2009
 group by t1.kostenplaats

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.45       0.57       4151       8312          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.46       0.58       4151       8312          0       99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     99999      99999      99999  SORT GROUP BY NOSORT (cr=8312 pr=4151 pw=0 time=561332 us cost=3769 size=3591054 card=66501)
     99999      99999      99999   MERGE JOIN  (cr=8312 pr=4151 pw=0 time=507153 us cost=3769 size=7889886 card=146109)
     99999      99999      99999    SORT JOIN (cr=4156 pr=4151 pw=0 time=249237 us cost=1692 size=1759968 card=97776)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=4151 pw=0 time=52081 us cost=1132 size=1759968 card=97776)
     99999      99999      99999    SORT JOIN (cr=4156 pr=0 pw=0 time=133876 us cost=2077 size=3577464 card=99374)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=20072 us cost=1132 size=3577464 card=99374)

********************************************************************************

select t1.kostenplaats,
       t1.som + t2.som
  from ( select kostenplaats, sum(wtg1 + wtg2 + wtg3 + wtg4 + wtg5 + wtg6 + wtg7 + wtg8 + wtg9) as som
           from omzet t1
          where jaar = 2010
          group by kostenplaats ) t1,
       ( select kostenplaats, sum(wtg10 + wtg11 + wtg12) as som
           from omzet t1
          where jaar = 2009
          group by kostenplaats ) t2
 where t1.kostenplaats = t2.kostenplaats

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.57       0.61       4151       8312          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.57       0.62       4151       8312          0       99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     99999      99999      99999  HASH JOIN  (cr=8312 pr=4151 pw=0 time=605556 us cost=3690 size=3423836 card=65843)
     99999      99999      99999   VIEW  (cr=4156 pr=4151 pw=0 time=274389 us cost=1574 size=1711918 card=65843)
     99999      99999      99999    HASH GROUP BY (cr=4156 pr=4151 pw=0 time=257379 us cost=1574 size=1185174 card=65843)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=4151 pw=0 time=48724 us cost=1132 size=1759968 card=97776)
     99999      99999      99999   VIEW  (cr=4156 pr=0 pw=0 time=197430 us cost=1876 size=1729026 card=66501)
     99999      99999      99999    HASH GROUP BY (cr=4156 pr=0 pw=0 time=175642 us cost=1876 size=2394036 card=66501)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=63446 us cost=1132 size=3577464 card=99374)

********************************************************************************

Mijn query duurt een fractie langer dan jouw query, de "cost" is ook hoger. Tot zover heb je gelijk.

Met data in cache nogmaals de test:

code:

select *
  from omzet

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    40001      0.66       0.82       4151      43904          0      599994
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    40003      0.66       0.82       4151      43904          0      599994

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
    599994     599994     599994  TABLE ACCESS FULL OMZET (cr=43904 pr=4151 pw=0 time=918676 us cost=1136 size=26999730 card=599994)

********************************************************************************

select t1.kostenplaats, sum(t1.wtg1 + t1.wtg2 + t1.wtg3 + t1.wtg4 + t1.wtg5 + t1.wtg6 + t1.wtg7 + t1.wtg8 + t1.wtg9 + t2.wtg10 + t2.wtg11 + t2.wtg12)
  from omzet t1
  join omzet t2 ON t1.kostenplaats = t2.kostenplaats and t1.jaar=2010 and t2.jaar=2009
 group by t1.kostenplaats

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.44       0.45          0       8312          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.44       0.45          0       8312          0       99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     99999      99999      99999  SORT GROUP BY NOSORT (cr=8312 pr=0 pw=0 time=433074 us cost=3769 size=3591054 card=66501)
     99999      99999      99999   MERGE JOIN  (cr=8312 pr=0 pw=0 time=406903 us cost=3769 size=7889886 card=146109)
     99999      99999      99999    SORT JOIN (cr=4156 pr=0 pw=0 time=132012 us cost=1692 size=1759968 card=97776)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=25106 us cost=1132 size=1759968 card=97776)
     99999      99999      99999    SORT JOIN (cr=4156 pr=0 pw=0 time=136224 us cost=2077 size=3577464 card=99374)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=21569 us cost=1132 size=3577464 card=99374)

********************************************************************************

select t1.kostenplaats,
       t1.som + t2.som
  from ( select kostenplaats, sum(wtg1 + wtg2 + wtg3 + wtg4 + wtg5 + wtg6 + wtg7 + wtg8 + wtg9) as som
           from omzet t1
          where jaar = 2010
          group by kostenplaats ) t1,
       ( select kostenplaats, sum(wtg10 + wtg11 + wtg12) as som
           from omzet t1
          where jaar = 2009
          group by kostenplaats ) t2
 where t1.kostenplaats = t2.kostenplaats

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6668      0.37       0.40          0       8312          0       99999
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6670      0.38       0.40          0       8312          0       99999

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 99  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     99999      99999      99999  HASH JOIN  (cr=8312 pr=0 pw=0 time=385772 us cost=3690 size=3423836 card=65843)
     99999      99999      99999   VIEW  (cr=4156 pr=0 pw=0 time=127746 us cost=1574 size=1711918 card=65843)
     99999      99999      99999    HASH GROUP BY (cr=4156 pr=0 pw=0 time=108690 us cost=1574 size=1185174 card=65843)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=26408 us cost=1132 size=1759968 card=97776)
     99999      99999      99999   VIEW  (cr=4156 pr=0 pw=0 time=162913 us cost=1876 size=1729026 card=66501)
     99999      99999      99999    HASH GROUP BY (cr=4156 pr=0 pw=0 time=139076 us cost=1876 size=2394036 card=66501)
     99999      99999      99999     TABLE ACCESS FULL OMZET (cr=4156 pr=0 pw=0 time=27859 us cost=1132 size=3577464 card=99374)

********************************************************************************

Jouw query duurt nu plotsklaps langer en de cost is hoger. Bij herhaalde testen krijg ik vergelijkbare resultaten (waarbij ook nog valt op te merken dat de sort_join en merge_join veel meer temp gebruiken dan een hash_join en hash_group_by).

Conclusie: je statement "toch stukkie simpeler en vriendelijker naar de DBA" is bewezen onjuist. Je statement had moeten zijn "de ansi query is voor sommigen van ons eenvoudiger om te lezen en onder bepaalde voorwaarden sneller op de database.". En dan zeik ik nog niet over het incompleet zijn van je query

Wat heb jij hier verder aan: helemaal niets. Het was enkel een mooie gelegenheid voor mij om iemand (niet jij, iemand buiten GoT) uit te leggen hoe tracing werkt in Oracle en wat kleine valkuilen daarbij te tonen.

[NMe]

JaQ schreef op dinsdag 19 oktober 2010 @ 17:03:
[...]

Conclusie: je statement "toch stukkie simpeler en vriendelijker naar de DBA" is bewezen onjuist.

...in Oracle. De topicstarter gebruikt zo te zien een T-SQL-variant en die zou best eens anders kunnen werken. Daarnaast is een ANSI-compatible query over het algemeen wél wenselijk, onder andere omdat dat de applicatie makkelijker te porten maakt naar andere systemen indien nodig.

[JaQ]

NMe schreef op dinsdag 19 oktober 2010 @ 18:05:
...in Oracle.

ach uiteraard. Ik noemde niet voor niets de exacte versie + OS van de testomgeving

Mijn excuses ik mis nuance

NMe schreef op dinsdag 19 oktober 2010 @ 18:05:
De topicstarter gebruikt zo te zien een T-SQL-variant en die zou best eens anders kunnen werken. Daarnaast is een ANSI-compatible query over het algemeen wél wenselijk, onder andere omdat dat de applicatie makkelijker te porten maakt naar andere systemen indien nodig.

Er is wel meer wenselijk, bijvoorbeeld goed nadenken over performance bij het ontwikkelen van queries en een datamodel. (hoeveel ontwikkelaars kunnen eigenlijk een trace maken en de uitkomsten beoordelen?)