Re: Logical and physical structure of time series (in ORACLE)

There is an important space- (and time-) saving mechanism available for the class of problem discussed in this mail thread if we assume that only a limited number of signal values are (can be) stored.

1. Maintain a separate table of TIME_SLICES (id number primary key, start_time date NOT NULL UNIQUE [, end_time date NOT NULL])

This table "rolls over". i.e. supposing that we store 50,000 rows (approximately 1 month's worth of minutes). The ID of the latest set of data to arrive is held in a separate table (everyone should have a table of "current/latest values"!)
When the next set of data arrives, it is given the next ID and the relevant row is updated with the actual time(s). When the 50,000th has been stored, the next ID reverts to 1.

2) Build a table SIGNAL_VALUES (time_id number foreign key references TIME_SLICES, signal_id number, signal_value float(32), constraint SV_PK primary key (time_id, signal_id)) storage (<this is fixed>).

(Pre-load this table with the maximum number of maximum-sized values that can be stored in order to avoid space management problems later)

3) When a bunch of signals arrives, they all get stored as *updates* to the set of rows with the current ID (from step 1) and the given signal_ID.
Note that there is no insertion, no space management and **no updating of indexed key values**.

This technique can speed up mass loads of transient data by a factor of 50 or more.

Hope this helps.

Chrysalis.

Peter Wieland wrote:
>
> Tim Spellman wrote:
> >
> > In article <32FF343A.4B36_at_scn.de>, Peter Wieland <wieland_at_scn.de> writes:
> > > 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)
 

> > One way to handle compressing time series data that makes SELECT statements
> > easier to write is to have a pair of time columns, one for the begin time,
> > and one for the end time. That is, the first time is the time the value
> > became valid, and the end time is the time that value expired. Then your
> > table becomes:
> >
> > id pk
> > begin_time pk
> > end_time pk
> > value
> >
> > The PK on end_time is to make SELECTs fast.
> >
> > > However, I usually have the following select:
 

> > > Select *
> > > from "gigant_table"
> > > where ID = 123
> > > and time > "12.3.97"
> > > and time < "15.3.97";
> >
> > This becomes:
> >
> > Select *
> > from gigant_table
> > where ID = 123
> > and begin_time > "12.3.97"
> > and end_time < "15.3.97";
> >
>
> Tim,
>
> thanks for your response, but I assume you will get the following problem:
>
> assume time sequence t1 < t2 < t3 < t4 < t5
>
> You store
>
> id: 123
> begin_time: t1
> end_time: t5
> value: 456
>
> You form a select with:
>
> Select *
> from gigant_table
> where ID = 123
> and begin_time > t2
> and end_time < t4
>
> oops, the id 123 is not selected even if the value 456 WAS valid
> at t3 !!!
>
> The way out: introduce a "maximum time interval" where the data is not stored,
> i.e. a value is stored at least each x (say 60) minutes ...
>
> Than you can do:
>
> Select *
> from gigant_table
> where ID = 123
> and begin_time > t3 - x
> and end_time < t3 + x
>
> sort the result and jump into the interval of need. But than this "jump"
> is executed by the extract application and if you do this, you can also
> assume that each value is valid until a new value comes into the system,
> i.e. you don't have to store the "end_time".
>
> Am I wrong?
>
> Peter
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> 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 Wed Mar 05 1997 - 00:00:00 CST