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

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)
> etc..

Peter,

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";

Performance is about as good as it can be in Oracle because you are using the primary key directly.

HTH

-- 
Tim Spellman                    | IntraNet, Inc. provides mission-critical
TSpellman_at_IntraNet.com          | electronic payment systems and related
617-527-3399 x564               | solutions worldwide.