pre-computed summary tables necessary with quality database design?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Terrence Brannon - 28 Dec 2004 23:27 GMT

Hi Everyone,

I have been in one or two places where people developed extra tables
that had pre-computed statistics about certain other tables, such as
sums and ratios.

I heard an Informix DBA say that such a thing was bad design. Is there
some other way to "tune" a database so that such things are
pre-computed and ready for quick retrieval upon aggregate-style
querying without going through the effort of pre-computing and storing
the results oneself?

I have spent my time using databases with largely exposure to MySQL,
Postgresql and SQLite and my suspicion is that the hand-holding I do
with these products (particularly MySQL) is not necessary in more
powerful products.

Signature

Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.

Reply to this Message

--CELKO-- - 29 Dec 2004 14:41 GMT

Generally speaking, you would use a VIEW in an OLTP database. This
way, the summary information is computed correctly at all times and
your do not have to be constantly updating the summary tables.

Physical reads and writes are slow part of an RDBMS; doing joins and
computations is several orders of magnitude faster. But more
importantly, it is better to have correct data a few seconds later than
wrong data fast.

If you have a data warehouse, then it will have summary data in cubes
and roll ups stored for faster access and statistical usage. Your data
warehouse product will provide structures for that kind of thing.
Google Teradata, Red Brick and Sand Technology for some examples.

Reply to this Message

Lennart Jonsson - 29 Dec 2004 20:04 GMT

DB2 has summary tables which is a kind of materialized view. I think
Oracle and MSQL has something similar.

HTH
/Lennart

Reply to this Message

ak_tiredofspam@yahoo.com - 29 Dec 2004 22:07 GMT

>> DB2 has summary tables which is a kind of materialized view. I think

Oracle and MSQL has something similar.
.. <<

IBM itself now prefer to name them MQT. And yes, Oracle has
materialized views, and when you create a clustered index on a view in
MS SQL, it is materialized

Reply to this Message