UPDATE and commit;

Subha,

Please read the reply of Jurij. Unless you change the following statement

"UPDATE TEST SET EMP_ID=C2.VAL_ID; " by having a where clause you are updating 26M records for each execution of this statement.

-amar

Well, here is a cleaned up copy of your code with the corrections that Jurij *alread* told you to make...

CREATE OR REPLACE PROCEDURE
---UPDATE_TESTING
IS
---CURSOR
------C1
---IS
------SELECT
---------L.EMP_ID
------------VAL_ID
------FROM
---------LOCATION L ,
---------TEST ------T
------WHERE
---------L.REL_DIRECTORY---=---T.REL_DIRECTORY;

---MY_COUNTER NUMBER(12);

BEGIN
---MY_COUNTER:=0;
---FOR C2 IN C1 LOOP
------UPDATE
---------TEST
------SET
---------EMP_ID---=---C2.VAL_ID
------WHERE
---------REL_DIRECTORY---=---C2.REL_DIRECTORY;
------MY_COUNTER---:=---MY_COUNTER---+---1---;
------IF ( MY_COUNTER---=---50000 )---THEN
---------MY_COUNTER:=0 ;
---------COMMIT;
------END IF;
---END LOOP;
END;



However, you are *still* fetching across commits. As Jurij *already* said, you should search this forum for maybe 'FETCH ACROSS COMMIT' to find multiple threads where we explain the issue and solutions.

HTH,

- Chris

In your procedure, as far as commit is concerned, you are commiting on every update instead on after 5000. That is because you have placed the commit not in the conditional block


for ....

if .... then
reset the counter;
COMMIT;
end if;

end loop;
--
-- Final commit to confirm
-- that all your data had been commited
--
COMMIT;

end;
/


Hope this would fix your commit issue.

Sam

Chris,

Don't you think that you have to select the REAL_DIRECTORY in your cursor, in-order to alias it in the update?

Sam

Yeah yeah yeah.... minor technicality;)


Good catch with the final COMMIT as well.

- Chris

CREATE OR REPLACE PROCEDURE UPDATE_TESTING
IS
CURSOR C1 IS SELECT L.EMP_ID VAL_ID,L.REL_DIRECTORY l_rel_dir,T.REL_DIRECTORY t_rel_dir FROM LOCATION L , TEST T WHERE
L.REL_DIRECTORY=T.REL_DIRECTORY;
MY_COUNTER NUMBER(12);
BEGIN
MY_COUNTER:=0;
FOR C2 IN C1 LOOP
UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE c2.l_rel_dir=c2.t_rel_dir ;
MY_COUNTER:=MY_COUNTER+1;
IF ( MY_COUNTER=50 )THEN
MY_COUNTER:=0 ;
COMMIT;
END IF;
END LOOP;
commit;
END;

I have created this as said .But this statement goes into a loop ...
The test table had about
SQL> select count(*) from test;

COUNT(*)
----------
44690

SQL> select USED_UREC from v$transaction;

USED_UREC
----------
175417

SQL> /

USED_UREC
----------
182627

and this is not commiting for every 50 records ...

Why would you want to compare the real directory on to that of the cursor?

UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE c2.l_rel_dir=c2.t_rel_dir ;


Shouldn't it be that of

UPDATE TEST SET EMP_ID=C2.VAL_ID WHERE real_directory =c2.t_rel_dir ;

Is there something else we are missing ????

Sam