MQT Help

[p175]

People,

if I have the following table table:

CREATE TABLE DATES_A (
DATE_A DATE NOT NULL WITH DEFAULT CURRENT DATE,
ID INTEGER NOT NULL ,
VALUE DEC(5,2) NOT NULL DEFAULT 0)
NOT LOGGED INITIALLY
IN RESULTS INDEX IN INDEXES;

.. which contains some 52962814 records, with 2635767 distinct ID's

... AND I need to calculate average VALUE, thus I create the following
MQT:

CREATE SUMMARY TABLE MQT_DATES_A AS
(SELECT ID AS ID,
COUNT(DATE_A) AS CNT,
DEC(AVG(VALUE),5,2) AS AVG_VAL
FROM DATES_A
GROUP BY ID)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
IN RESULTS INDEX IN INDEXES;

... What is the best way to index the source table or indeed re-write
the entire thing so that the MQT REFRESH statement doesn't take forever
to run ? Obviously I can't use INCREMENTAL refreshes as they don't seem
to work for the AVG scalar function. Is it possible to calculate the
average using someother means ? Any ideas on how to make this more
efficient ?

As always, most grateful for all responses.

Tim

[ChrisC]

p175 wrote:

People,

if I have the following table table:

CREATE TABLE DATES_A (
DATE_A DATE NOT NULL WITH DEFAULT CURRENT DATE,
ID INTEGER NOT NULL ,
VALUE DEC(5,2) NOT NULL DEFAULT 0)
NOT LOGGED INITIALLY
IN RESULTS INDEX IN INDEXES;

.. which contains some 52962814 records, with 2635767 distinct ID's

... AND I need to calculate average VALUE, thus I create the following
MQT:

CREATE SUMMARY TABLE MQT_DATES_A AS
(SELECT ID AS ID,
COUNT(DATE_A) AS CNT,
DEC(AVG(VALUE),5,2) AS AVG_VAL
FROM DATES_A
GROUP BY ID)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
IN RESULTS INDEX IN INDEXES;

.. What is the best way to index the source table or indeed re-write
the entire thing so that the MQT REFRESH statement doesn't take forever
to run ? Obviously I can't use INCREMENTAL refreshes as they don't seem
to work for the AVG scalar function. Is it possible to calculate the
average using someother means ? Any ideas on how to make this more
efficient ?

As always, most grateful for all responses.

Tim

I don't think there is much hope of getting the full refresh to be very
fast on this - it has to scan the full 5.3 million row base table for
each refresh.

Are you sure AVG doesn't work with INCREMENTAL? If so, you can always
do that work yourself. You can do this by keeping the SUM and COUNT
for each ID and doing the AVG by hand in the query. So, the MQT is:

CREATE SUMMARY TABLE MQT_DATES_A AS
(SELECT ID AS ID,
COUNT(DATE_A) AS CNT,
SUM(VALUE) AS SUM_VAL
FROM DATES_A
GROUP BY ID)
DATA INITIALLY DEFERRED
REFRESH DEFERRED
IN RESULTS INDEX IN INDEXES;

Then, get the AVG later by:

SELECT DEC(SUM_VAL / CNT, 5, 2) as AVG_VAL, ...
FROM MQT_DATES_A

Or, if you want to use the base table, the following might work (I
haven't tried this, so I don't know if the optimizer will use it):

SELECT DEC(SUM(VALUE) / COUNT(DATE_A), 5, 2) as AVG_VALUE, ...

-Chris

[Knut Stolze]

ChrisC wrote:

Are you sure AVG doesn't work with INCREMENTAL?

Unfortunately, it is really not supported yet.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany