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 <[email protected] <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 [email protected]<javascript:>
>> To unsubscribe from this group, send email to
>> [email protected] <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 [email protected] <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 [email protected]
To unsubscribe from this group, send email to
[email protected]
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
