Re: snapshot data: can oracle somehow compress it?

pete_at_mynix.org wrote:
> Well, we have been doing that up till now, coding from-to style
> historization by hand. It is cumbersome and error prone, and
> complicates all queries (... between fromdate and todate all over the
> place, complex joins and indexes etc). The point is, I'd like Oracle
> to take care of it under the hood, if possible.

Well, no, oracle does no macig there. You've got to design this like any other db app.
Ok, oracle has time and interval types so the entering and updating should be no problem (two tables, couple of dml statements).

As for querying, if you always only want data for a specific date, you could do this with a view and some kind of control table to hold the dates you want to query. But again, no way of doing this automatically.

Another way to shield your developers from this would be a few pl/sql functions for use in the queries. ( ... where dateIs ('2002-02-15',FromDate,ToDate) Don't know about the performance impact but I expect for there to be one.

>
>> Much easier and it's especially designed to put
>> files in archives storing only the deltas. Retrieving is a breeze
>> too. And it's fast.
>>
>> How much data are we talking about anyway?
>
> About 2 G per day,

Checked this with ci (rcs). Gnu diff gave up on comparing two 1G files. However, the subset ci wants is something you could program yourself so that could in theory work.

> so saving each snapshot would get us about 500G per
> year. And lots of complex queries, so file based stuff is out of the
> question.

How many rows would that be?

Can you do a second table for a relation between days and the data with the intervals? Would it be worth it in terms of data reduction?

Greetings!
Volker

--
While it is a known fact that programmers
never make mistakes, it is still a good idea
to humor the users by checking for errors at
critical points in your program.
-Robert D. Schneider, "Optimizing INFORMIX
 Applications"