Re: VARRAYS for VLDB

Mixing VARRAYs with heavy duty processing doesn't sound like a good idea.

It depends very much on exactly how you want to use the data, but your best bet could be a range-partitioned IOT, partitioned by time,  with compressed primary key - and a materialized view, with the same partitioning and on a prebuilt table, for the summary figures.

The order of the columns in the IOT PK will be dictated by the nature of the most important queries.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____USA_(FL)_May 2nd
____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Karsten Schmidt" <groups_at_karsten-schmidt.com> wrote in message
news:c6711ac4.0304230403.4d3f19c1_at_posting.google.com...


> Hi,

>  I am about to decide on the physical datamodel for a rather large

> database,

>  I would appreciate some feedback before it is all messed up.

>

>  There is a number of measures, that are sampled with a certain

> frequency.

>  i.e. temperature in centigrades @15 samples per second. I get

several


> hundred/ thousand of these streams, and the data is to be kept for

at


> least three months.

>

>  The interesting part is the table that stores the samples:

>  Partitioned by timestamp, subpartitioned by sample type, or

something


> like that.

>  This might even become a index-organized table. Not sure about that

> yet, I will see how the prototype works out.

>  This is to hold several billion rows.

>

>  To cut down on the overhead for primary key values etc., I was

> thinking about using a varray to hold the actual sample values - so

i


> can get maybe 50 samples per row in that table.

>

>  Then in the table, i can keep several statistics such as number of

> samples, average, etc.

>

>  The whole intend is to get the storage requirements into a

reasonable


> range (i.e. less than a terabyte) for that table.

>

>  To get the logical view back, I would create a view that rolls out

> that varray,

>  so the application sees indeed one 'pseudo-' row per sample.

>

>  Is this reasonable, or is there any showstopper with varrays that

you


> know about ?

>

> Thanks for your help

>  Karsten