Storing time/value data

[toddyl]

Hi,

I am collecting data, using Perl, on a per minute basis from a large number of systems. The columns on the data I am pulling back are: server, process, day, time, value. For example: myserver, proc1, 2008/01/18, 09:00, 12345

There is a huge amount of this data that I now need to load into my Oracle 10g database. Given the sheer size of this data (in excess of 1 million rows) I am wondering if anyone could please advise on the best way of storing this data in my Oracle database.

Some considerations is that in future I may need to know a value for a specific time -- ie: I may need to know what the value was on 2008/01/18 at 10:35am. Also, I want to be able to get the max, min and average values etc. for the day.

Thanks for your help,

Toddyl

 

[Dagon]

1 million actually isn't a lot by modern database standards. Do you mean 1 million in total or 1 million per day or week ?

 

[toddyl]

I mean 1 million per day. This has the potential to grow further over the next few months. I also need to keep this data for 90 days.

 

[Dagon]

It sounds like the best bet would be a partitioned table, probably one partition for each day. You could then drop the oldest partition when you reach 90 days.

 

[toddyl]

IS there any type of Oracle Data type I could use for storing each time value combination in?

Ideally I would like to have 1 row per server, per process, per day and then in a datatype have all the interval and value combinations. I still need to be able to access this interal and value combination though.

This would be something like:

ID SERVER PROC DAY DATA
1 myserver1 proc1 2008/1/17 "09:00 12345, 09:01 12348, 09:02 12350 ...... "
2 myserver1 proc1 2008/1/18 "09:00 12300, 09:01 12398, 09:02 12450 ...... "

 

[Dagon]

I would just use a standard Oracle date. Using to_char functions, you can convert this to any format you like (day of week, time in yy:mm:ss format etc).

 

[jimirvine]

I would also strongly suggest that you do not consider any further storing the data as:
09:00 12345, 09:01 12348, 09:02 12350 ....

Store each time and value pair as a separate row and separate columns. If needs be you can create a view (or even materialised view) to make it easier to extract theinfo that you are looking for. (The kind of code you would be looking for could be found by searching for the keyword PIVOT)

 

[Dagon]

Yes, attempting to store a million pieces of data as a single string, which you presumably keep updating every day, would be mad. Querying would be horrendous.

Another tool you could use for walking through the data would be the LEAD and LAG functions. These would enable you to see the next or previous entries so that you can compare them, compute differences etc.

 

[Thargy]

Also,

consider a range partition by date, so that you can easily narrow down your queries to just the days of interest, and not have to look through the whole table.

T

Grinding away at things Oracular

 

[toddyl]

Guys,

I am not considering having 1 row with a million entries in it.

The idea would be to have 1 row per server, proc, day combination. I was thinking in the last field I could store the minute data from 9am to 5pm -- times and values. I was wondering about LOBs for this. I know nothing of these and am curious if I can still access these using SQL to find a particular time and value pair.

Thanks for your help so far.

 

[jimirvine]

I am not considering having 1 row with a million entries in it.

Glad to hear it. I didn't think that you were.

1 row per server, proc, day combination

Bad idea. 1 row, 1 transaction i.e. 1 time/process/server/day
Otherwise you will spend all of your time parsing the strings that you have built. Trust me, forget about that approach.