Creating a matrix spread

From: Mike Winterer <mikew3_at_corp.earthlink.net>
Date: Tue, 09 Nov 1999 10:22:03 -0800
Message-ID: <3828664B.C7A76EF4@corp.earthlink.net>

I have an interesting situation that I could use some help with. The description of the problem follows:

I have records listing a users start and stop session times plus the the seconds the user was logged in. I want to create a summary table that that lists each user and the amount of time that was used during each of every 15 minute block of time over the 24 hour period. For example:

Given the following user record:

USER RECORD

USERID       jdoe                        (Primary Key)
STARTTIME    01-OCT-1999 00:09:15        (Primary Key)
ENDTIME      01-OCT-1999 01:18:41        (Primary Key)
SECONDS      3266

I want to produce the following spread:

TIME SLOT SECONDS_USED

-------------- ------------
00:00 - 00:15             0
00:15 - 00:30           345
00:30 - 00:45           900
00:45 - 01:00           900
01:00 - 01:15           900
01:15 - 01:30           221
01:30 - 01:45             0

...
...
...

23:45 - 00:00 0

Note that the above is the results of a single user record. There will be a weeks worth of data for the user that will be summarized into a single record.

The records will be stored in two tables as follows:

SUMM_WEEK

USERID         VARCHAR2(50)
WEEKENDING     DATE

TOTAL_SECONDS NUMBER
(...other information...)

SUMM_WEEK_DETAIL

USERID         VARCHAR2(50)
WEEKENDING     DATE
TIME_SLOT      DATE
SECONDS        NUMBER

The intent is to summarize the weeks worth of data and spreading it across all of the 15 minute time slots of the 24 hour day. From this data it will be possible to analyse patterns of usage.

The final and key part of this problem is not how to do this. I have experimented with with one method that uses a PL/SQL table to spread the data and insert the necessary records in the tables. The problem is the time that it is taking to do this. Already in excess of 14 hours. The set of user records is about 12 million rows per week and due to double by the middle of next year. Obviously, this is not a solution that will work.

The original data for the users is being loaded via SQL*Loader in the form of commas delimited records. These originally come from RADIUS data for internet sessions. There is currently a fair amount of processing done on the unix side to prepare these records for loading. The tools used to prepare the data for loading are perl and C. The platform is Solaris 2.6 and Oracle 8.0.5.1.0

I have thought of several ways to approach the problem, including a trigger on the table to create the spread when the user record is inserted, preparing the data before it is loaded and producing a separate load file for the spread data, etc. A core consideration is to load the data for a single week in one process rather than incrementally loading the data for the week, although I am not wedded to that idea.

What I am looking for is ideas on how to process this kind of volumne in a resonable amount of time. I will gladly entertain a diverse set of ideas not limited to an Oracle procedure.

Please do not waste everyone's time questioning or criticizing the value of doing this in the first place. Depending on the test results of this experiment I will need to determine whether the value provided exceeds the effort and processing time.

Thanks

Mike Winterer
mailto:mikew3_at_corp.earthlink.net

"Sleep is a wholly inadequate substitute for coffee." Received on Tue Nov 09 1999 - 12:22:03 CST