Here is the result of explain : http://pastebin.com/MKt4XxdF Here is the result for plan : sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where (temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide = 'CHUL' and ( more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or more>more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = '201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in ('CHIR', 'MED', 'OBS') and (rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup; SELECT: identifier 'more' unknown sql>plan select temps_mois.rfoperyea as c0, temps_mois.rfoperdmo as c1, rfovsn.rfovsnide as c2, rfoadv_1.rfoadvsup as c3, rsaaev_2.rsaaevsup as c4, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco' then dwhinv.dwhinvqte else 0 end)) as m0, sum((case when dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16' then dwhinv.dwhinvqte else 0 end)) as m1 from rfoper_temps_mois as temps_mois, dwhinv as dwhinv, rfovsn as rfovsn, rfoadv as rfoadv_1, rsaaev as rsaaev_2 where (temps_mois.rfoper___rforefide = 'CHUL') and (dwhinv.dwhinv___rforefide = 'CHUL' and ( more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = 'cli_nb_entrees_mco') or more>(dwhinv.dwhinv___rfodomide = 'CLINIQUE' and dwhinv.dwhinv___rfoindide = '16'))) and dwhinv.dwhinvdtd = temps_mois.rfoperdtd and temps_mois.rfoperyea in ('2011', '2012') and temps_mois.rfoperdmo in ('01', '02') and (rfovsn___rforefide = 'CHUL') and dwhinv.dwhinv___rfovsnide = rfovsn.rfovsnide and rfovsn.rfovsnide = '201204_reel' and (rfoadv_1.rfoadv___rfovdeide='MCO' AND rfoadv_1.rfoadvrvs=1 AND rfoadv_1.rfoadv___rforefide= 'CHUL') and dwhinv.dwhinv_d2rfodstide = rfoadv_1.rfoadvinf and rfoadv_1.rfoadvsup in ('CHIR', 'MED', 'OBS') and (rsaaev_2.rsaaev___rsavedide='PRISE_EN_CHARGE' AND rsaaev_2.rsaaevrvs=1 AND rsaaev_2.rsaaev___rforefide= 'CHUL') and dwhinv.dwhinv___rsaedtide = rsaaev_2.rsaaevinf and rsaaev_2.rsaaevsup = 'REG_HOSPI_SEANCE' group by temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide, rfoadv_1.rfoadvsup, rsaaev_2.rsaaevsup; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | rel | +===============================================================================================================================================================================+ | project ( | | | group by ( | | | | project ( | | | | | join ( | | | | | | join ( | | | | | | | join ( | | | | | | | | join ( | | | | | | | | | join ( | | | | | | | | | | join ( | | | | | | | | | | | select ( | | | | | | | | | | | | table(axabas.dwhinv) [ dwhinv.dwhinv___rforefide NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfovsnide NOT NULL, dwhinv.dwhinv___rfoind | : ide NOT NULL, dwhinv.dwhinvqte, dwhinv.dwhinvdtd NOT NULL, dwhinv.dwhinv_d2rfodstide, dwhinv.dwhinv___rsaedtide, dwhinv.%TID% NOT NULL ] COUNT : | | | | | | | | | | ) [ dwhinv.dwhinv___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], dwhinv.dwhinv___rfodomide NOT NULL = varchar(32)[char(8) "CLINIQUE"], (dwhinv.dwhinv_ | : __rfoindide NOT NULL = varchar(32)[char(18) "cli_nb_entrees_mco"]) or (dwhinv.dwhinv___rfoindide NOT NULL = varchar(32)[char(2) "16"]), dwhinv.dwhinv___rfoindide NOT NULL in : : (varchar(32)[char(18) "cli_nb_entrees_mco"], varchar(32)[char(2) "16"]) ], : | | | | | | | | | | select ( | | | | | | | | | | | | table(axabas.rfoper_temps_mois) [ rfoper_temps_mois.rfoper___rforefide as temps_mois.rfoper___rforefide, rfoper_temps_mois.rfoperyea as temps_mois.rfoper | : yea, rfoper_temps_mois.rfoperdmo as temps_mois.rfoperdmo, rfoper_temps_mois.rfoperdtd as temps_mois.rfoperdtd, rfoper_temps_mois.%TID% NOT NULL as temps_mois.%TID% ] COUNT : | | | | | | | | | | ) [ temps_mois.rfoper___rforefide = varchar(32)[char(4) "CHUL"], temps_mois.rfoperyea in (varchar(32)[char(4) "2011"], varchar(32)[char(4) "2012"]), temps_ | : mois.rfoperdmo in (varchar(32)[char(2) "01"], varchar(32)[char(2) "02"]) ] : | | | | | | | | | ) [ dwhinv.dwhinvdtd NOT NULL = temps_mois.rfoperdtd ], | | | | | | | | | | select ( | | | | | | | | | | | table(axabas.rfovsn) [ rfovsn.rfovsn___rforefide NOT NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL ] COUNT | | | | | | | | | | ) [ rfovsn.rfovsn___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], rfovsn.rfovsnide NOT NULL = varchar(32)[char(11) "201204_reel"] ] | | | | | | | | | ) [ dwhinv.dwhinv___rfovsnide NOT NULL = rfovsn.rfovsnide NOT NULL ], | | | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT NULL as ade2.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as ade2.rfoade___rfovdeide, rfoade.rfo | : ade_i_rfodstide NOT NULL as ade2.rfoade_i_rfodstide, rfoade.rfoadervs NOT NULL as ade2.rfoadervs, rfoade.rfoadegch NOT NULL as ade2.rfoadegch, rfoade.rfoadedrt NOT NULL as a : : de2.rfoadedrt, rfoade.%TID% NOT NULL as ade2.%TID% ] COUNT : | | | | | | | ) [ dwhinv.dwhinv_d2rfodstide = ade2.rfoade_i_rfodstide NOT NULL ], | | | | | | | | select ( | | | | | | | | | table(axabas.rfoade) [ rfoade.rfoade___rforefide NOT NULL as ade1.rfoade___rforefide, rfoade.rfoade___rfovdeide NOT NULL as ade1.rfoade___rfovdeide, rfoade.rfo | : ade_i_rfodstide NOT NULL as ade1.rfoade_i_rfodstide, rfoade.rfoadervs NOT NULL as ade1.rfoadervs, rfoade.rfoadegch NOT NULL as ade1.rfoadegch, rfoade.rfoadedrt NOT NULL as a : : de1.rfoadedrt, rfoade.%TID% NOT NULL as ade1.%TID% ] COUNT : | | | | | | | ) [ ade1.rfoadervs NOT NULL = int[tinyint "1"], ade1.rfoade___rfovdeide NOT NULL = varchar(32)[char(3) "MCO"], ade1.rfoade___rforefide NOT NULL = varchar(32)[cha | : r(4) "CHUL"], ade1.rfoade_i_rfodstide NOT NULL in (varchar(32)[char(4) "CHIR"], varchar(32)[char(3) "MED"], varchar(32)[char(3) "OBS"]) ] : | | | | | | ) [ ade2.rfoade___rforefide NOT NULL = ade1.rfoade___rforefide NOT NULL, ade2.rfoade___rfovdeide NOT NULL = ade1.rfoade___rfovdeide NOT NULL, ade2.rfoadervs NOT NU | : LL = ade1.rfoadervs NOT NULL, ade2.rfoadegch NOT NULL >= ade1.rfoadegch NOT NULL, ade2.rfoadedrt NOT NULL <= ade1.rfoadedrt NOT NULL ], : | | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as aed2.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as aed2.rsaaed___rsavedide, rsaaed.rsaaedr | : vs NOT NULL as aed2.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as aed2.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed2.rsaaedgch, rsaaed.rsaaeddrt NOT NULL as aed2. : : rsaaeddrt, rsaaed.%TID% NOT NULL as aed2.%TID% ] COUNT : | | | | | ) [ dwhinv.dwhinv___rsaedtide = aed2.rsaaed_i_rsaedtide NOT NULL ], | | | | | | select ( | | | | | | | table(axabas.rsaaed) [ rsaaed.rsaaed___rforefide NOT NULL as aed1.rsaaed___rforefide, rsaaed.rsaaed___rsavedide NOT NULL as aed1.rsaaed___rsavedide, rsaaed.rsaaedr | : vs NOT NULL as aed1.rsaaedrvs, rsaaed.rsaaed_i_rsaedtide NOT NULL as aed1.rsaaed_i_rsaedtide, rsaaed.rsaaedgch NOT NULL as aed1.rsaaedgch, rsaaed.rsaaeddrt NOT NULL as aed1. : : rsaaeddrt, rsaaed.%TID% NOT NULL as aed1.%TID% ] COUNT : | | | | | ) [ aed1.rsaaedrvs NOT NULL = int[tinyint "1"], aed1.rsaaed___rforefide NOT NULL = varchar(32)[char(4) "CHUL"], aed1.rsaaed___rsavedide NOT NULL = varchar(32)[char(1 | : 5) "PRISE_EN_CHARGE"], aed1.rsaaed_i_rsaedtide NOT NULL = varchar(32)[char(16) "REG_HOSPI_SEANCE"] ] : | | | | ) [ aed2.rsaaed___rforefide NOT NULL = aed1.rsaaed___rforefide NOT NULL, aed2.rsaaed___rsavedide NOT NULL = aed1.rsaaed___rsavedide NOT NULL, aed2.rsaaedrvs NOT NULL = | : aed1.rsaaedrvs NOT NULL, aed2.rsaaedgch NOT NULL >= aed1.rsaaedgch NOT NULL, aed2.rsaaeddrt NOT NULL <= aed1.rsaaeddrt NOT NULL ] : | | | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, temps_mois.%TID% NOT NULL, dwhinv.dwhinv___rfodomide NOT NULL, dwhinv.dwhinv___rfoindide NOT NULL, dwhinv.dwhinvqte, dwhi | : nv.%TID% NOT NULL, rfovsn.rfovsnide NOT NULL, rfovsn.%TID% NOT NULL, ade1.rfoade___rforefide NOT NULL as rfoadv_1.rfoadv___rforefide, ade1.rfoade___rfovdeide NOT NULL as rfo : : adv_1.rfoadv___rfovdeide, ade1.rfoadervs NOT NULL as rfoadv_1.rfoadvrvs, ade1.rfoade_i_rfodstide NOT NULL as rfoadv_1.rfoadvsup, ade2.rfoade_i_rfodstide NOT NULL as rfoadv_1 : : .rfoadvinf, ade1.%TID% NOT NULL, ade2.%TID% NOT NULL, aed1.rsaaed___rforefide NOT NULL as rsaaev_2.rsaaev___rforefide, aed1.rsaaed___rsavedide NOT NULL as rsaaev_2.rsaaev___ : : rsavedide, aed1.rsaaedrvs NOT NULL as rsaaev_2.rsaaevrvs, aed1.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevsup, aed2.rsaaed_i_rsaedtide NOT NULL as rsaaev_2.rsaaevinf, aed : : 1.%TID% NOT NULL, aed2.%TID% NOT NULL ] : | | ) [ temps_mois.rfoperyea, temps_mois.rfoperdmo, rfovsn.rfovsnide NOT NULL, rfoadv_1.rfoadvsup NOT NULL, rsaaev_2.rsaaevsup NOT NULL ] [ temps_mois.rfoperyea, temps_mois.rf | : operdmo, rfovsn.rfovsnide NOT NULL, rfoadv_1.rfoadvsup NOT NULL, rsaaev_2.rsaaevsup NOT NULL, sys.sum no nil (sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.d : : whinv___rfodomide NOT NULL, varchar(32)[char(8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(18) "cli_nb_entrees_mco"]))), boolean "false", sys.a : : nd(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char(8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(18) "cli_nb_entrees_mco"]))), dwhin : : v.dwhinvqte, decimal(18,6)[tinyint "0"])) as L1, sys.sum no nil (sys.ifthenelse(sys.ifthenelse(sys.isnull(sys.and(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char( : : 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(2) "16"]))), boolean "false", sys.and(sys.=(dwhinv.dwhinv___rfodomide NOT NULL, varchar(32)[char( : : 8) "CLINIQUE"]), sys.=(dwhinv.dwhinv___rfoindide NOT NULL, varchar(32)[char(2) "16"]))), dwhinv.dwhinvqte, decimal(18,6)[tinyint "0"])) as L2 ] : | ) [ temps_mois.rfoperyea as L.c0, temps_mois.rfoperdmo as L.c1, rfovsn.rfovsnide NOT NULL as L.c2, rfoadv_1.rfoadvsup NOT NULL as L.c3, rsaaev_2.rsaaevsup NOT NULL as L.c4, | : L1 as L1.m0, L2 as L2.m1 ] : +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 35 tuples (3.899ms) sql>