Last Updated: September 09, 2019
·
396
· bturchyn

Materialized Query Tables

Sometimes, it's the little things that make me giddy to do database work. Like this little gem I found in the IBM DB2 documentation:

CREATE TABLE STRANS AS
     (SELECT YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM
      FROM TRANS
      GROUP BY YEAR, MONTH, DAY)
      DATA INITIALLY DEFERRED REFRESH DEFERRED;

Let's say you have a large table of transactions. Rather than running queries directly against a table to get aggregate values of your net transaction costs for a given day, this table builds all the entries automagically for each day.