Data logging with Oracle (or any DBMS)
What is the "best" (correct, standard, etc.) way to log data received at a given rate (every minute, every 5 seconds, every 10ms, etc.) in an Oracle database?
It seems inefficient to store a 7 byte DATE value for each datatop (especially as the frequency increases). However, packing the data into some type of raw format makes statistics and other calculations on the data difficult.
I assume this question is general enough to apply to any DBMS, but in this case I am using Oracle.
a source to share
How much does a terabyte of disk cost and compacting those 7 bytes really worth the effort? If you want to calculate statistics and log reports over time, it will be very painful not to use the date for use in SQL queries.
When Oracle is just logging data to a table, try not to write too many or too many indexes to the log table. Make sure the log table is partitioned from day one into manageable sizes - this could be a partition per day, week, or month depending on how much data you generate. Create your housekeeping policy from day 1.
When you add a new partition at the end of your "period" when data starts to flow into the new partition, you might consider using "alter partition move compress" to compress the data so that it can be stored on the network in less space.
There are many options, you just need to think through the requirements that you should try and find the best solution. Depending on what you are doing, entering a file may also be an option - but beware of thousands upon thousands of files in the same directory, which can also cause problems.
a source to share
Since each line of collected data must stand on its own, you must use space to write the full DATE value - unless you want to use something like a Unix timestamp (whole seconds from 1970-01-01 00: 00: 00Z or whatever) or another suitable epoch or milestone). This 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 very convenient, but it is relatively compact.
a source to share