Re: max concurrent record query

From: Randy DeWoolfson <randy_at_euclidsys.com>
Date: Wed, 23 Jun 1999 20:18:13 -0400
Message-ID: <37717944.3D91E8EC@euclidsys.com>

I dont have an instance handy to check this out... and my syntax is probably off.. but you get the idea...

first, i structured your table as:

table_name: log

---

id number(10) _- (pk)
online date,
offline date

this can be a view of your existing tables i think, or you can change what i do below to do the joins required... then query something like:

SELECT a.online, count( a.id ) online_count FROM log a
WHERE a.id IN (

    SELECT b.id,
    FROM log b
    WHERE a.online BETWEEN b.online AND b.offline )
GROUP BY a.id;

essentially, this is supposed to count up all the people online each time someone new logs in. You dont need to do something every 5 minutes or other arbitrary time...

also, you can put a MAX( COUNT( a.id )) probably to find the one entry where the most users were online.

or alternately, ORDER BY online_count and see the pattern.

hope this gives some insight. - i thought it was a good problem :) randy

"Daniel A. Morgan" wrote:

> > Anyways, here's the gist of the problem: one of the requests that came in
> > is to know what the maximum number of users that were logged in at any
> > one time, and how many were there.
>
> How about creating a results table and putting insert triggers on each of
> your two existing tables. The results table could have one field for the
> date, one for the time interval and one for total users. A person logging
> on would cause the counter for that date and time segment to be
> incremented. Someone logging off would decrement the same field.
>
> An alternative would be to just perform a count from v$session at some
> point in the five minute interval and save the total number of records to a
> report table.
>
> select count(*)
> from v$session
> where username is not null;
>
> Daniel A. Morgan
Received on Wed Jun 23 1999 - 19:18:13 CDT