Logical and physical structure of time series (in ORACLE)

From: Peter Wieland <wieland_at_scn.de>
Date: 1997/02/10
Message-ID: <32FF343A.4B36@scn.de>#1/1

I try to find a good solution for a logical and physical database design for the following problem in ORACLE:

I get every minute a huge amount of pairs like (s;v) into my database, with

s: Signal-ID (integer)
v: value for this signal (float)

that means a time series arrives at my database:

t1: (s1;v1) (s2;v2) (s3;v3) ... (s4000;v4000) t2: (s1;v1) (s2;v2) (s3;v3) ... (s4000;v4000) etc..

where t1 is time 1, t2 is time 2 and so on. This t's happens acually every minute, i.e. I receive 4000 (ID;value) pairs per minute in my database. The requirement is to collect these data for one year (max of about 2.26 billion (ID;value) pairs are in my database). I know that this is much ...

Thus I decided to reduce data via some algorithms like "If values of a signal doesn't change significant .e.i. more than 5%, don't store a new value", etc.
I got some of these reduction algorithms and thay are tested so they are NOT the problem.
Because of this, my real life time series looks somehow like:

t1: (s1;v1) (s2;v2) (s3;v3) ... (s4000;v4000)
t2:         (s2;v2) (s3;v3) ... 
t3:                 (s3;v3) ... 
t4: (s1;v1) (s2;v2)         ... 
t5: (s1;v1)         (s3;v3) ...

etc..

I know that because of this I have to expand data again during selects. Reduction just lead to the fact that
for some time points, the values are NOT available in the DB but must be calculated by selecting the next newer and the next older value and perform some interpolation.

However, I usually have the following select:

Select *
from "gigant_table"
where ID = 123
and time > "12.3.97"
and time < "15.3.97";

My question is how is the BEST database design?

Case: I do nothing but putting all pairs into a huge table like:

	| ID  | value | time |
	|  s1 |  v1   |  t1  |
	|  s2 |  v2   |  t1  |
	|  s3 |  v3   |  t1  |
      etc.
	|  s1 |  v1   |  t2  |
         etc.

+ easy to realize

bad performance, difficult to maintain (huge table, index, etc)

===> forget it!

2. Case: O.k. so I split my data into several small tables ======== e.g. one per month or one for first 100 signals-ID's,

         100 for the next, and so on. Create a view to combine
         these physical tables into one logical.

+ easy to handle

performance, still the data is somewhere in the blocks of the tables

===> forget it!

3. Case: Clustering?

I would like to have the data for ID 123 sorted and compressed in few blocks, like this:

s1: (t1;v1)(t2;v2)(t3;v3) etc.

Can I get this by clustering my table? What is the cluster Key? If I understand right I can cluster using ID, but still the (t1;v1) pairs are unsorted, like

s1: (t968;v987)(t2;v2)(t345;v345) etc.

clustering takes long time for inserts (and I have a lot of them)
performance, still the data is unsorted inside the blocks 4. Case: Temporary table and sort and cluster?

I could use a temporary table "actual" to collect actual values. Once a while (e.g. when I collected enough values for a single signal to fill up one data block), I could transfer the values from this table into my real (clustered) table and sort the pairs after time before inserting them.

How exactly do I have to organize it?
Would this solve my problem?
Is there a better way to do it?
Did I forget something? 5. Case: Complex data structure?

Of course I always can design other table structures to reduce the number of rows in my table, e.g. by collecting one hour of signal values in a single row, like:

this will not work fine if I got some reduction, I also need the time ...

and I could collect a specific amount (depends on DB-Block-size) of (time/value/status) pairs within a single row of the table.

+ I assume that this will complicate my selects extremly ???

Thanks In advance

Peter Wieland

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        Peter Wieland -- http://www.iai.fzk.de/~wieland/
 o                                        o
 O\/    mailto:wieland_at_scn.de      o===L_//   Home:+47/22357757
_L/__,  Remember: Telemark is it!    (_) (_)   Job:+47/22633343
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Received on Mon Feb 10 1997 - 00:00:00 CST