470,628 Members | 2,251 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,628 developers. It's quick & easy.

MQT Help

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

Aug 3 '06 #1
2 1402

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

Aug 4 '06 #2
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
Aug 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Tom | last post: by
6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
5 posts views Thread by Steve | last post: by
8 posts views Thread by Mark | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.