Why can’t I use SELECT … FOR UPDATE with aggregate functions?

I have an application where I find a sum() of a database column for a set of records and later use that sum in a separate query, similar to the following (made up tables, but the idea is the same):

SELECT Sum(cost)
INTO v_cost_total
FROM materials
WHERE material_id >=0
AND material_id <= 10; 

[a little bit of interim work]

SELECT material_id, cost/v_cost_total
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10
FOR UPDATE;

However, in theory someone could update the cost column on the materials table between the two queries, in which case the calculated percents will be off.

Ideally, I would just use a FOR UPDATE clause on the first query, but when I try that, I get an error:

ORA-01786: FOR UPDATE of this query expression is not allowed

Now, the work-around isn’t the problem – just do an extra query to lock the rows before finding the Sum(), but that query would serve no other purpose than locking the tables. While this particular example is not time consuming, the extra query could cause a performance hit in certain situations, and it’s not as clean, so I’d like to avoid having to do that.

Does anyone know of a particular reason why this is not allowed? In my head, the FOR UPDATE clause should just lock the rows that match the WHERE clause – I don’t see why it matters what we are doing with those rows.

EDIT: It looks like SELECT … FOR UPDATE can be used with analytic functions, as suggested by David Aldridge below. Here’s the test script I used to prove this works.

SET serveroutput ON;

CREATE TABLE materials (
    material_id NUMBER(10,0),
    cost        NUMBER(10,2)
);
ALTER TABLE materials ADD PRIMARY KEY (material_id);
INSERT INTO materials VALUES (1,10);
INSERT INTO materials VALUES (2,30);
INSERT INTO materials VALUES (3,90);

<<LOCAL>>
DECLARE
    l_material_id materials.material_id%TYPE;
    l_cost        materials.cost%TYPE;
    l_total_cost  materials.cost%TYPE;

    CURSOR test IS
        SELECT material_id,
            cost,
            Sum(cost) OVER () total_cost
        FROM   materials
        WHERE  material_id BETWEEN 1 AND 3
        FOR UPDATE OF cost;
BEGIN
    OPEN test;
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
    FETCH test INTO l_material_id, l_cost, l_total_cost;
    Dbms_Output.put_line(l_material_id||' '||l_cost||' '||l_total_cost);
END LOCAL;
/

Which gives the output:

1 10 130
2 30 130
3 90 130

Answer

The syntax select . . . for update locks records in a table to prepare for an update. When you do an aggregation, the result set no longer refers to the original rows.

In other words, there are no records in the database to update. There is just a temporary result set.

Advertisement

Answer