Incremental aggregation : PL/SQL

Question

I have this use case where I have to do incremental aggregation of data that comes in.

I have a table A which has the following columns

id,insertion_time,key,col2,col3

i have to calculate result = col2*col3 for each key and find the max in an hour and put to another table B with columns.

key,hour,max

so the condition is, if I am going to run the same procedure after a while, if new data has been inserted into the table A, the procedure should loop through only the new records to process.

What I have done till now :

I have created a temporary table C to store the last insertion_timestamp that was processed as last_timestamp. Each procedure execution will check for this value and will select only the entries which have insertion_time > last_timestamp.

then the proc will do the following:

get the entry last_timestamp from table C
select the maximum value of col1*col2 group by key and hour where insertion_timestamp > lasttimestamp
for each result check if an entry for (key,hour) pair is present in B

a) if present - update the entry if new col1*col2 value is greater than existing value
b) if not, insert the entry

Is this the right way of doing incremental aggregation in relational databases? Or is there any better approach?

Thanks

won't that be a performance issue? wont it be better to do the same after a bunch of inserts? correct me if i am wrong. — vishnu viswanath
Depends on your performance requirements. With appropriate indexes, updating B to the greater of its existing value and the multiple from the newly inserted record is a simple task; whereas updating B from an aggregation over A to find the maximum multiple of all records within a given hour is relatively more complex. However, if you need every drop of performance upon insertion and can afford to update B offline, then maybe it's worth considering. Personally, I'd use a trigger until performance dictates otherwise: remember Knuth's maxim, "premature optimisation is the root of all evil". — eggyal
thank you. i will probably test both the cases and see which is better. — vishnu viswanath

Codo Codo · Accepted Answer · 2014-01-26T10:41:49

Yes, your approach seems ok. I can't think of any other one that would be considerably better:

PROCEDURE CALC_HOURLY_MAX
IS
    l_last_timestamp DATE;
    l_max_insertion_time DATE;
    l_value NUMBER;

BEGIN
   SELECT last_timestamp into l_last_timestamp FROM ...;

   FOR r IN (
       SELECT MAX(col2 * col3) max_value, key,
         TRUNC(insertion_time, 'HH24') hour,
         MAX(insertion_time) max_insertion_time
       FROM A
       WHERE insertion_time > l_last_timestamp
       GROUP BY key, TRUNC(insertion_time, 'HH24')
   ) LOOP

       BEGIN
         SELECT max into l_value FROM B where key = r.key and hour = r.hour;

         IF l_value < r.max_value THEN
            UPDATE B set max = r.max_value where key = r.key and hour = r.hour;
         END IF;

       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           INSERT INTO B (key, hour, max) VALUES(r.key, r.hour, r.max_value);

       END;

       IF l_max_insertion_time  is null OR l_max_insertion_time < r.max_insertion_time THEN
         l_max_insertion_time := r.max_insertion_time;
       END IF;
   END LOOP;

   -- store highest processed insertion time
   UPDATE XX SET last_timestamp = l_max_insertion_time WHERE ...;
END;

Please not that this code might not compile as the column names MAX and HOUR as symbols in Oracle. You might want to choose different names.

Furthermore, if the time your transactions take varies, then the insertion time - as seen from other transactions that only see committed data - might not be strictly increasing. So it might be a good approach to select somewhat further back into the past, e.g.:

WHERE insertion_time > l_last_timestamp - 3 / 24 / 3600

Incremental aggregation : PL/SQL

1 Answers