PL/SQL - do not update changed rows

One way is to use FOR UPDATE SKIP LOCKED such that other sessions won't be able to pick the rows which are already picked for update.

For example,

Session 1:

SQL> SELECT empno, deptno  2    FROM emp  WHERE  3   deptno = 10  4  FOR UPDATE NOWAIT;     EMPNO     DEPTNO---------- ----------      7782         10      7839         10      7934         10SQL>

Session 2:

SQL> SELECT empno, deptno  2    FROM emp  WHERE  3   deptno in (10, 20)  4  FOR UPDATE NOWAIT;  FROM emp  WHERE       *ERROR at line 2:ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Now let's skip the rows which are locked by session 1.

SQL> SELECT empno, deptno  2    FROM emp  WHERE  3   deptno IN (10, 20)  4  FOR UPDATE SKIP LOCKED;     EMPNO     DEPTNO---------- ----------      7369         20      7566         20      7788         20      7876         20      7902         20SQL>

So, department = 10 were locked by session 1 and then department = 20 are locked by session 2.

I have done something like your problem but my table isn't too huge like your.

I re-designed my table, added 2 columns.

created_date: A Trigger put sysdate when insert data.

modified_date: A Trigger put sysdate when update data.

Then I can use created_date or modified_date in my where clause.

Example:

UPDATE TABLE table_nameSET column_name = 'values'WHERE created_date < SYSDATE;

I hope this will help you.

Oh, I absolutely forgot about this question.So, I ended up making a snapshot of current rows by saving their copies to another table (I had to update only those rows that satisfied given condition). After that I updated rows that haven't changed their values (using merge).