Data Logging with Oracle (or any RDBMS)

Question

What is the "best" (correct, standard, etc) way to maintain a log of data acquired at a set rate (every minute, every 5 seconds, every 10ms, etc) in an Oracle database?

It seems inefficient to store the 7 byte DATE value for every datapoint (especially as the frequency increases). However, packing the data into some type of raw format makes statistics and other calculations on the data more difficult.

I guess this question is general enough to apply to any RDBMS, but in this case I'm using Oracle.

Answer 1

Since each row of data collected has to stand on its own, you have to use the space to record the complete DATE value - unless you choose to use something like a Unix timestamp (integer seconds since 1970-01-01 00:00:00Z, or some other suitable epoch or reference point). That fits into 4 bytes to give a 68-year period on either side of the epoch (assuming signed 32-bit integers). It may not be quite as convenient, but it is relatively compact.

Answer 2

How much does it cost for a terabyte of disk, and is compacting those 7 bytes really worth the effort? If you want to calculate stats and reports on the logs based on time, its going to be very painful to uncompact the date to use in SQL queries.

With Oracle just log the data to a table - try not to log too much or have too many indexes on the log table. Make sure your log table is partitioned from day 1 to managable sizes - that could be a partition per day, week or month depending on how much data you are generating. Design your housekeeping policy from day 1 too.

When you are adding a new partition at the end of your 'period' when the data starts going into the new partition, you could consider using 'alter partition move compress' to compress the data to store it online in less space.

There are a lot of options, you just need to think through the requirements you have to try and find the best solution. Depending on what you are doing, logging to a file could be an option too - but beware of thousands and thousands of files in a single directory which can cause trouble too.