DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Caching results of formulas during a select

  1. #1
    Join Date
    Jan 2004
    Posts
    17

    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?

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have a look at the DETERMINISTIC clause ...
    http://download-west.oracle.com/docs...ps.htm#1005493
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2004
    Posts
    17
    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  8. #8
    Join Date
    Jan 2004
    Posts
    17
    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.

  9. #9
    Join Date
    Jan 2004
    Posts
    162
    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.

  10. #10
    Join Date
    Jan 2004
    Posts
    17
    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
  •  


Click Here to Expand Forum to Full Width