Re: query: "count" each minute between start/end

From: Nikolas Tautenhahn <virtual_at_gmx.de>
Date: Sat, 28 Jul 2007 18:12:49 +0200
Message-ID: <f8fpu1$mjf$02$1@news.t-online.com>

Hi,

Michel Cadot wrote:
> "Nikolas Tautenhahn" <virtual_at_gmx.de> a écrit dans le message de news: f8fhlj$4il$03$1_at_news.t-online.com...
> It depends if you want each and every minute or only the minutes when
> there is someone.

It would be sufficient to get only the minutes when there is someone. I got this by removing an outer join:

with cal as

       ( select sysdate - (1/12) +(rownum-1)/1440 tim
         from dual
         connect by level <= 120 )
     select cal.tim, sum(decode(testlog.testlog_c_date,null,0,1)) "nbIn"
     from cal, testlog
     where testlog.testlog_c_date >= sysdate - (1/12) and 		 

testlog.testlog_c_date < cal.tim and
(testlog.testlog_c_date(+)+testlog.testlog_x_duration(+)/86400) >= cal.tim

     group by cal.tim
     order by cal.tim

this was done with real data and was fast for the example above (going 2hrs into history took 1.6 seconds, 6hrs took 20 seconds and going back 18hrs took 180s... well and this calculation would be needed for the last 21days with approx. 5000-8000 datasets per day.

Unfortunately I don't have a sqlplus at the moment but only the sqldeveloper. Its output of explain plan was exported to html and the source code uploaded to:

http://np.cryosphere.de/p:f02423f7c5f0ce859a727395673715c0/plain

All other exports were less readable, sorry for the mess...

Regards,
N. Received on Sat Jul 28 2007 - 11:12:49 CDT