Re: snapshot data: can oracle somehow compress it?

"Volker Hetzer" <volker.hetzer_at_ieee.org> writes:

> 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,

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.

> 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, 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.

Thanks,

-- 
Peter Mutsaers, Dübendorf, Switzerland.