0

In oracle: employees table contain more than 10 million data. For update I'm using rownum for faster update.

CREATE OR REPLACE PROCEDURE emp_up AS 
begin
  loop 
      update employees set commission_pct=0 where commission_pct is null and rownum <=10000; 
      exit when sql%notfound;  
      commit;
  end loop;
end;

In postgresql: postgresql does not support rownum. Is there any way to update employees table in postgres with the similar way in oracle?

  • 2
    That approach is not "faster" in Oracle - it's actually slower. One large transaction is almost always faster than many smaller ones - at least if the UNDO and REDO tablespaces are sized correctly. Updating 10 million rows shouldn't take that long to actually need "batched" updates. – a_horse_with_no_name Jul 25 '18 at 11:14
  • update employees set commission_pct=0 where commission_pct is null; whats the different between this and questioned syntax? if i use only this syntax – Mojib Jul 26 '18 at 4:59
1

In PostgreSQL a function is always be executed in one transaction. It does not matter if you loop over batches of 10.000 rows or just UPDATE the whole table.

Thanks to MVCC there should also no need to split the UPDATE in order to allow other processes to do something on the table, as the locks are very lightweight in this scenario.

So, the correct replacement would be:

CREATE OR REPLACE FUNCTION emp_up() AS
$func$
  update employees set commission_pct=0 where commission_pct is null 
$func$
LANGUAGE plpgsql;
-3

You can try this query:

UPDATE employees
    SET commission_pct=0
FROM
(
    SELECT *, Row_Number() OVER (ORDER BY employeeid) as rownum
    FROM employees 
) SubQuery
where SubQuery.commission_pct is null 
  and SubQuery.rownum <=10000
  • You would have to move the other filter into the subquery. – CL. Jul 26 '18 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.