I do have indexes on these tables.
it took around 45 mits to run this.
I even tried to use a temporary table and did merge update.
There is a function that gets called to compute a value.. tried to test on
that.
i will update my findings..
Thanks again.
On Mon, Mar 25, 2013 at 1:20 PM, ddf <orat...@msn.com> wrote:
>
>
> 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> 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
>>>
>>> To unsubscribe from this group, send email to
>>> Oracle-PLSQL...@**googlegroups.com
>>>
>>> For more options, visit this group at
>>> http://groups.google.com/**group/Oracle-PLSQL?hl=en<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.
>>>
>>> For more options, visit
>>> https://groups.google.com/**groups/opt_out<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.
>
>
>
--
--
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.
