White paper or source for Materialized tables?

[--CELKO--]

Is there a good source for the performance details of Materialized
Query Tables? I have to worry about how often to refresh them in
UDB. Yes, I know that is very vague.

[Ian]

Re: White paper or source for Materialized tables?

--CELKO-- wrote:

Is there a good source for the performance details of Materialized
Query Tables? I have to worry about how often to refresh them in
UDB. Yes, I know that is very vague.

Joe,

As you'd expect, it depends completely on the specific MQT. A couple
of things to consider:

1) When you do a full refresh of an MQT, DB2 effectively does a
DELETE FROM MQT and then INSERT INTO MQT. These are logged
operations, so if you MQT is large this can take a while.

You can help this by turning off logging during the refresh.
Or, you can even (since V9.1) truncate the MQT with "load from
/dev/null replace into MQT" to nearly eliminate the delete
phase of the full refresh. Obviously, both of these options
have recoverability consequences, so you have to be careful.

2) Incremental refresh (via a staging table) is really great, if
you can use it.

3) During an MQT refresh, there is an exclusive lock on the table
(not sure if it's a Z or X lock), so there are availability
considerations if you're refreshing the tables while users are
querying the database.


Also: Refresh frequency is mostly a function of the users' tolerance
for stale data. Of course, all users will tell you that they can't
tolerate any stale data. ;-)



Thanks -


Ian Bjorhovde

[--CELKO--]

Re: White paper or source for Materialized tables?

>Of course, all users will tell you that they can't tolerate any stale data. <<

LOL! I tell people that Dr. Land did not invent the Polaroid camera.
He invented the "ten second eternity" -- we used to wait for week to
get black and white photographs developed at a drug store, but after
the Polaroid camera, we go crazy waiting ten seconds for a color
picture.