Caching results of formulas during a select
I regularly have to process data in a table (2 million rows+) and update a few fields (MEASUREMENT1 in this example) with conversions or averaged values. Many of the original values are the same eg there could be 1000+ rows with the value 123 in the measurement field.
I can do most of the conversions within a select statement. However this is slow because for each row, the value in each ‘measurement’ field has the formula applied to it.
CREATE OR REPLACE PROCEDURE process_data
AS
CURSOR test_update IS
SELECT x_pos, y_pos, round(power(2,MEASUREMENT1/12),8) the_result, done
FROM measurement_table
ORDER BY x_pos, y_pos
FOR UPDATE OF measurement1, done;
current_row test_update%ROWTYPE;
converted_measurement FLOAT;
BEGIN
OPEN test_update;
LOOP
FETCH test_update INTO current_row;
EXIT WHEN test_update%NOTFOUND;
converted_measurement := current_row.the_result;
-- other stuff happens here with x_pos y_pos
UPDATE measurement_table
SET measurement1 = converted_measurement, done = 1
WHERE CURRENT OF test_update;
END LOOP;
CLOSE test_update;
commit;
END;
/
Another approach is not to do the conversion during the select statement but instead do it for each new measurement value and store the conversion in a lookup table. This means the amount of time spent doing conversions is reduced because after the conversion has been done once the value can be looked up in ‘power_lookup1’.
CREATE OR REPLACE PROCEDURE process_data
AS
CURSOR test_update IS
SELECT x_pos, y_pos, MEASUREMENT1 the_result, done
FROM measurement_table
ORDER BY x_pos, y_pos
FOR UPDATE OF measurement1, done;
current_row test_update%ROWTYPE;
converted_measurement FLOAT;
TYPE power_lookup IS TABLE OF FLOAT INDEX BY VARCHAR2(25);
power_lookup1 power_lookup;
BEGIN
OPEN test_update;
LOOP
FETCH test_update INTO current_row;
EXIT WHEN test_update%NOTFOUND;
-- see if current measurement is already in the lookup table
IF power_lookup1.EXISTS(to_char(current_row.the_result)) = false THEN
-- This conversion has not yet been done so perform it & add to lookup table
power_lookup1(to_char(current_row.the_result)) := round(power(2,current_row.the_result/12),8);
END IF;
-- get power from lookup table
converted_measurement := power_lookup1(to_char(current_row.the_result));
-- other stuff happens here with x_pos & y_pos
UPDATE measurement_table
SET measurement1 = converted_measurement, done = 1
WHERE CURRENT OF test_update;
END LOOP;
CLOSE test_update;
commit;
END;
/
The lookup table version is about 5 times quicker than the original. This code is a very simple example of what actually takes place in the real table. The real table contains many more formulas so I would prefer not to create lots of lookup tables. Is there any technique or Oracle Hint that will cache the result of the power formula and can ideally be done directly in a select statement?
