Re: Design opinions for large data sets

In article <26MAY199309583117_at_erich.triumf.ca>, grant_at_erich.triumf.ca (Peter A. Grant) writes:
>
> I am just playing with how we might go about storing "real data" in ORACLE.
> For the past six years, we have used ORACEL to store what I'd describe as
> "static" information about our facility. We are considering storing "live"
> data, but there are a lot of data points.
>
> We have programs which carry out routine logging of a large number of data
> points every five minutes. Call it a sample.
>
> There are 20 Groups, which are identified by a two-character name. Each
> group has 500 Items to be read and stored as something like a NUMBER(12,6),
> and we need a date/time Timestamp.
>
> I've set up SQL statements to do the following:
>
> 1. Set up a table which has just four columns: Group, Item, Timestamp, and
> value. This is the easiest, but you end up replicating the Timestamp
> up to 10,000 per sample. We'll end up with 1,051,200,000 rows a year.
>
> 2. Set up a table with 502 columns (okay, it'll have to be a view of at
> least two table since ORACLE restricts you to no more than 254 columns
> per table). A fair amount of duplication, and 20 rows make up a sample.
> Now just 2,102,400 rows per year.
>
> 3. Set up a table with 10001 columns (again, a view of at least 40 tables).
> This then only have one row per sampling, but inserting the data is
> going to be really ugly. I think you end up with just as much
> duplication as you did in the above approach beacause you have to store
> the Timestamp in each of the tables that make up the view. Logically,
> however, this appeals to me. Almost a realistic 105,120 rows per year.

How about packing your 10,000 sample values into a LONG field. It's not as nice but if you're working in a 3GL then you can still manipulate the data quite easily.

OR

Pack your 10,000 into a number of VARCHAR2 columns.

Both of the above make it difficult to do fast querries on the stored values.

Bruce... pihlab_at_hhcs.gov.au Received on Fri May 28 1993 - 05:27:15 CEST