Re: snapshot data: can oracle somehow compress it?

pete_at_mynix.org wrote:
> We have an app that stores daily snapshots of data, such as:
>
> day1, a1, b1, c1
> day2, a1, b1, c1
> day3, a1, b1, c2
> day4, a1, b1, c2
>
> etc
>
> In general, much of the data won't change between two days. Therefore
> storing full snapshots is extremely wasteful w.r.t. diskspace.
>
> I was wondering/thinking that Oracle should be able to store such
> snapshots more intelligently while creating the illusion of daily full
> snapshots:
>
> In the example above it might be physically stored as:
>
> from-day1, to-day2, a1, b1, c1
> from-day3, to-day4, a1, b1, c2
>
> i.e. as long as no column except for the date column changes, all
> dates could be captured in a single physical record with a from/to
> range for the date.
>
> Currently we do such things manually, but that is quite tedious and
> prone to errors.
>
> Is there some feature or option in Oracle to do this for us?
What you need is tables, triggers, pl/sql and sql. Then you can design appropriate Tables for the base data, triggers for the delta generation and the"compressing" and procedures for expanding the data during queries. Don't know whether you can define a view with that function or how efficient that would be.
Of course, you might want to change your application so that it can use the compressed data without the expansion. You might also want to read a book about database design, like "oracle design" or "an introduction to database systems".

If this is too much bother and a database is no requirement, have you considered a version control system instead? 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?

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"