Snapshot function?

Mairhtin O'Feannag

Hello all ...

hopefully without sounding too obtuse, we've run into a case where we
need to persist the contents of a view for performance reasons. As I
understand it (and I don't really) Oracle has something called a
snapshot, which "automatically" persists the contents of a view and can
determine the delta of the contents somehow. This would be PERFECT for
this application, since the data only updates monthly maybe, but in
chunks of 5-10 million rows. Truncating the table (excue my Oracle-like
language, I mean importing/replacing with an empty delimited file
<smile>) and persisting the view all over again is gonna be an "over the
weekend" kind of task - 25 hours we estimate.

Can anyone suggest (without flaming) a way to accomodate what appears to
be a useful Oracle feature?

TIA,

Maiirhtin O'Feannag

Nov 12 '05 #1

Leo

Take a look at materialized query tables (MQT's). It sounds like you
need to create one refresh deferred and then you need to create a
staging table to go along with it. Then you could do a delta refresh
rather than having to refresh the entire table.


"Mairhtin O'Feannag" <ir********@rocketmail.com> wrote in message news:<Xn******************************@64.164.98.7 >...

Hello all ...

hopefully without sounding too obtuse, we've run into a case where we
need to persist the contents of a view for performance reasons. As I
understand it (and I don't really) Oracle has something called a
snapshot, which "automatically" persists the contents of a view and can
determine the delta of the contents somehow. This would be PERFECT for
this application, since the data only updates monthly maybe, but in
chunks of 5-10 million rows. Truncating the table (excue my Oracle-like
language, I mean importing/replacing with an empty delimited file
<smile>) and persisting the view all over again is gonna be an "over the
weekend" kind of task - 25 hours we estimate.

Can anyone suggest (without flaming) a way to accomodate what appears to
be a useful Oracle feature?

TIA,

Maiirhtin O'Feannag

Nov 12 '05 #2

Serge Rielau

It's called a Materialized Query Table (MQT) in DB2.
Check out the CREATE TABLE statement and SET INTEGRITY.
You can refresh yourself, or you can have DB2 refresh for you
immediately or at a later time.

Cheers
Serge

Nov 12 '05 #3

Mairhtin O'Feannag

Serge,

Kewl. But does a refresh mean a rebuild? Is it that way for Oracle? A
rebuild would be the same as we are doing now, which is to create table
like view and select * from the view and insert into the created table.
What were trying to do is avoid the rebuild of the entire contents, since
it will be on the order of 100 million rows.

Thanks,

M

Serge Rielau <sr*****@ca.eye-bee-em.com> wrote in news:L9VUc.422132
$r**********@news01.bloor.is.net.cable.rogers.com:

It's called a Materialized Query Table (MQT) in DB2.
Check out the CREATE TABLE statement and SET INTEGRITY.
You can refresh yourself, or you can have DB2 refresh for you
immediately or at a later time.

Cheers
Serge

Nov 12 '05 #4

Serge Rielau

DB2 can do incremental refresh automatically or upon request (SET
INTEGRITY).
There ar esoem classes where refresh can be done and other where it can't.
E.g. if you have a query with the MIN and MAX for groupings.
That can be maintained incrementally.

Cheers
Serge

Nov 12 '05 #5