Why can't I use SELECT ... FOR UPDATE with aggregate functions?

Question

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 1

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.

Answer 2

You might try something like:

<<LOCAL>>
declare
  material_id materials.material_id%Type;
  cost        materials.cost%Type;
  total_cost  materials.cost%Type;
begin
  select material_id,
         cost,
         sum(cost) over () total_cost
  into   local.material_id,
         local.cost,
         local.total_cost 
  from   materials
  where  material_id between 1 and 3
  for update of cost;

  ...

end local;

The first row gives you the total cost, but it selects all the rows and in theory they could be locked.

I don't know if this is allowed, mind you -- be interesting to hear whether it is.

Answer 3

Is your problem "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."?

In that case , probably you can simply use a inner query as:

SELECT material_id, cost/(SELECT Sum(cost)
  FROM materials
  WHERE material_id >=0
  AND material_id <= 10)
INTO v_material_id_collection, v_pct_collection
FROM materials
WHERE material_id >=0
AND material_id <= 10;

Why do you want to lock a table? Other applications might fail if they try to update that table during that time right?

Answer 4

For example, there is product table with id, name and stock as shown below.

product table:

id	name	stock
1	Apple	3
2	Orange	5
3	Lemon	8

Then, both 2 queries below can run sum() and SELECT FOR UPDATE together:

SELECT sum(stock) FROM (SELECT * FROM product FOR UPDATE) AS result;

WITH result AS (SELECT * FROM product FOR UPDATE) SELECT sum(stock) FROM result;

Output:

 sum
-----
  16
(1 row)

Answer 5

For that, you can use the WITH command.

Exemple:

WITH result AS (
  -- your select
) SELECT * FROM result GROUP BY material_id;