-
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?
-
-
Silmdave,
Can we use DETERMINISTIC function in this example?
As per the manual, it can be use only when
The function must return the same output return value for any given set of input argument values.
I think round(power(2,MEASUREMENT1/12),8) the_result may return different values based on MEASUREMENT1 column value.
Tamil
-
If measurement1 is an input to a user-defined function that calculates round(power(2,MEASUREMENT1/12),8) then the function can be marked as deterministic.
-
I put the calculation into ..
CREATE OR REPLACE FUNCTION F1 (P1 NUMBER) RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN round(power(2,P1/12),8);
END;
/
and called it from the select statement
CURSOR test_update IS
SELECT x_pos, y_pos, F1(MEASUREMENT1) the_result, done
FROM measurement_table
ORDER BY x_pos, y_pos
FOR UPDATE OF measurement1, done;
In my test table there are 176000 records and 873 unique values of measurement1.
The time taken was 1m15s.
The original with the calculation in the select took 1m07s
The lookup table version took 38s
Looks as though using this approach has slowed it down a bit.
-
Oracle does not check for determinism so you can mark any function you like as DETERMINISTIC whether it is or not.
AFAIK DETERMINISTIC functions are *NOT* cached by Oracle on the fly in SQL (whatever Oracle Docs. might imply). Where the result of the function is stored in a Function-based index it may be used in place of re-calculating the function. The DETERMINISTIC keyword is simply a flag for Function-based index eligibility.
Fastest approach here would be a single SQL - either update or better still CTAS into new table if applicable. Question is really what 'other stuff happens here with x_pos y_pos' and whether it can be done in-line in SQL. Rarely do I see a cursor for..loop written because it 'could not be done in SQL' that *really* could not be done in SQL.
-
Looks like for this relatively simple formula, the cost of the function call is comparible with the evaluation of the function. In a complex case it might not be so "bad". (P.S. padders is pointing out that I'm being naïve here)
I'm a little concerned about the logic of your process (it could be that you have over-simplified it for posting here):
- After running it measurement1 is replaced by f1(original_measurement1). If you run it again, it would be replaced by f1(f1(original_measurement1)). Should you have a DONE<>1 condition in the cursor?
- I would argue against over-writing the original measurement with a derived value. On philosphcal grounds and the practical grounds that if you preserve the original value, it's much easier to sort things out if you have an "accident" with the calculations.
Last edited by DaPi; 08-27-2004 at 06:05 AM.
-
The xpos, ypos has spatial calculations applied to track distance between the values. I use to run it all using an update where x = y structure and it took twice as long. Mainly because it was doing a lot of table scans.
-
Post full example of UPDATE statement that is causing problem.
A simple UPDATE statement is *FUNDAMENTALLY* more efficient and hence typically much faster than an equivalent PL/SQL loop with UPDATE however many clever caching mechanisms you may implement.
-
I have fixed the problem using bulk fetch / insert statments. Thanks for the help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|