On Monday, March 25, 2013 10:47:28 AM UTC-6, GD wrote: > > Thanks David.. for checking. > > Please ignore the fill_tab. > > Its only updating fk for pk value. > > Modified code is as below - > DECLARE > CURSOR rec_cur IS > SELECT * > FROM test4; > > TYPE num_tab_t IS TABLE OF NUMBER(38); > > pk_tab NUM_TAB_T; > fk_tab NUM_TAB_T; > BEGIN > OPEN rec_cur; > LOOP > FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab LIMIT 100000; > EXIT WHEN pk_tab.COUNT() = 0; > > FORALL i IN pk_tab.FIRST .. pk_tab.LAST > UPDATE test > SET fk = fk_tab(i) > WHERE pk = pk_tab(i); > > DBMS_OUTPUT.put_line('Updated '|| pk_tab.count || ' rows of test4 > table'); > > commit; > > END LOOP; > CLOSE rec_cur; > commit; > END; > / > > I did verify the total records that matches the PK. it should have done > all those records, but it let go 27893 records not updated in first round. > Here is the stats - > > 1st execution didn't update 27893 rows > 2nd execution didn't update 1573 rows > 3rd execution didn't update 3 rows > Finally 4th execution completed all the rows. > > > On Sat, Mar 23, 2013 at 9:19 AM, ddf <ora...@msn.com <javascript:>> wrote: > >> >> >> On Friday, March 22, 2013 3:00:08 PM UTC-7, GD wrote: >>> >>> Hi All, >>> >>> Have a question related to FORALL Bulk Update: >>> >>> I have a similar code as below - >>> >>> DECLARE >>> CURSOR rec_cur IS >>> SELECT * >>> FROM test4; >>> >>> TYPE num_tab_t IS TABLE OF NUMBER(38); >>> TYPE vc2_tab_t IS TABLE OF VARCHAR2(4000); >>> >>> pk_tab NUM_TAB_T; >>> fk_tab NUM_TAB_T; >>> fill_tab VC2_TAB_T; >>> BEGIN >>> OPEN rec_cur; >>> LOOP >>> FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab, fill_tab LIMIT >>> 100000; >>> EXIT WHEN pk_tab.COUNT() = 0; >>> >>> FORALL i IN pk_tab.FIRST .. pk_tab.LAST >>> UPDATE test >>> SET fk = fk_tab(i) >>> , fill = fill_tab(i) >>> WHERE pk = pk_tab(i); >>> >>> DBMS_OUTPUT.put_line('Updated '|| pk_tab.count || ' rows of >>> test4 table'); >>> >>> commit; >>> >>> END LOOP; >>> CLOSE rec_cur; >>> commit; >>> END; >>> / >>> >>> There are totally 531503 row to update. >>> >>> Even though dbms output show that it updated all the rows. >>> >>> Update 100000 rows of adjitem table >>> Update 100000 rows of test4 table >>> Update 100000 rows of test4 table >>> Update 100000 rows of test4 table >>> Update 100000 rows of test4 table >>> Update 31503 rows of test4 table >>> >>> There are some records those didn't get updated. >>> >>> 1st execution didn't update 27893 rows >>> 2nd execution didn't update 1573 rows >>> 2rd execution didn't update 3 rows >>> >>> What would be the reason, that its not updating everything in first >>> execution? >>> >>> Thanks in advance! >>> G >>> >>> You do not mention which tables are not getting 'complete' updates -- I >> can only presume the updates are for the pk table, the fk table and the >> fill table in that order. You're also selecting from a fourth table -- >> there is no guarantee that every record from test4 matches keys in the pk >> table, the fk table and the fill table. >> >> You need to check your data in all four tables before you start thinking >> your code has problems. >> >> >> David Fitzjarrell >> >> -- >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to oracle...@googlegroups.com<javascript:> >> To unsubscribe from this group, send email to >> oracle-plsql...@googlegroups.com <javascript:> >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> >> --- >> You received this message because you are subscribed to the Google Groups >> "Oracle PL/SQL" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to oracle-plsql...@googlegroups.com <javascript:>. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > Running on 11.2.0.3 I get:
SQL> create table test4( 2 pk number, 3 fk number, 4 constraint t4_pk primary key (pk) 5 ); Table created. SQL> SQL> create table test( 2 pk number, 3 fk number, 4 update_dt date, 5 constraint t_pk primary key (pk) 6 ); Table created. SQL> SQL> begin 2 for i in 1..500000 loop 3 insert into test4(pk, fk) 4 values(i, i); 5 6 insert into test(pk) 7 values(i); 8 end loop; 9 10 commit; 11 12 end; 13 / PL/SQL procedure successfully completed. SQL> SQL> DECLARE 2 CURSOR rec_cur IS 3 SELECT pk, fk 4 FROM test4; 5 6 TYPE num_tab_t IS TABLE OF NUMBER(38); 7 8 pk_tab NUM_TAB_T; 9 fk_tab NUM_TAB_T; 10 updt_ct number; 11 BEGIN 12 OPEN rec_cur; 13 LOOP 14 FETCH rec_cur BULK COLLECT INTO pk_tab, fk_tab LIMIT 100000; 15 EXIT WHEN pk_tab.COUNT = 0; 16 17 FORALL i IN pk_tab.FIRST .. pk_tab.LAST 18 UPDATE test 19 SET fk = fk_tab(i), update_dt = sysdate 20 WHERE pk = pk_tab(i); 21 22 select count(*) into updt_ct from test where fk is not null; 23 24 DBMS_OUTPUT.put_line('Updated '|| pk_tab.count || ' rows of test table. Not null count:'||updt_ct); 25 26 commit; 27 28 END LOOP; 29 CLOSE rec_cur; 30 commit; 31 END; 32 / Updated 100000 rows of test table. Not null count:100000 Updated 100000 rows of test table. Not null count:200000 Updated 100000 rows of test table. Not null count:300000 Updated 100000 rows of test table. Not null count:400000 Updated 100000 rows of test table. Not null count:500000 PL/SQL procedure successfully completed. SQL> SQL> select count(*) 2 from test 3 where update_dt is null; COUNT(*) ---------- 0 SQL> How long is this process taking? Do you have indexes on the two tables in question? David Fitzjarrell -- -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en --- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To unsubscribe from this group and stop receiving emails from it, send an email to oracle-plsql+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.