By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

Snapshot function?

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.