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