Home » SQL & PL/SQL » SQL & PL/SQL » Using FORALL (Oracle 9i)
Using FORALL [message #353881] Wed, 15 October 2008 07:53 Go to next message
discoverer
Messages: 30
Registered: January 2007
Member
Hi all,
I have a requirement where I need to update a column in 18 lakhs of records.I tried to do it using BULK COLLECT INTO and FORALL , but it is not showing any significant improvement in performance, infact performance is good when I use normal cursor loop for updating. Using FORALL is taking 25 min , whereas normal cursor loop is taking 17 min. I am confused. I am pasting the code. Kindly provide any suggestions to improve its performance. Thanks in advance.


USING FORALL :
***************

DECLARE
CURSOR cur_tcb
IS
SELECT tcb.viewname viewname, tcb.parentpartnumber parentpartnumber,
tcb.parentpartorg parentpartorg,
tcs.view_date_created view_date_created
FROM tcb, tcs
WHERE tcs.ptc_number = tcb.parentpartnumber
AND tcs.plant = tcb.parentpartorg
AND tcb.viewname IS NULL;

TYPE tcb_tab IS TABLE OF cur_tcb%ROWTYPE;
TYPE tcb_viewname IS TABLE OF tcb.viewname%TYPE;
TYPE tcb_parentpartnumber IS TABLE OF tcb.parentpartnumber%TYPE;
TYPE tcb_parentpartorg IS TABLE OF tcb.parentpartorg%TYPE;
TYPE tcs_view_date IS TABLE OF tcs.view_date_created%TYPE;

v_tcb tcb_tab := tcb_tab ();
v_tcb_parentpartnumber tcb_parentpartnumber := tcb_parentpartnumber ();
v_tcb_parentpartorg tcb_parentpartorg := tcb_parentpartorg ();
v_tcs_view_date tcs_view_date := tcs_view_date ();
BEGIN
OPEN cur_tcb;
LOOP
FETCH cur_tcb
BULK COLLECT INTO v_tcb LIMIT 1000;

FOR i IN 1 .. v_tcb.COUNT
LOOP
v_tcb_parentpartnumber.EXTEND;
v_tcb_parentpartnumber (i) := v_tcb (i).parentpartnumber;
v_tcb_parentpartorg.EXTEND;
v_tcb_parentpartorg (i) := v_tcb (i).parentpartorg;
v_tcs_view_date.EXTEND;
v_tcs_view_date (i) := v_tcb (i).view_date_created;
END LOOP;

FORALL i IN 1 .. v_tcb.COUNT
UPDATE tcb
SET viewname = v_tcs_view_date (i)
WHERE parentpartnumber = v_tcb_parentpartnumber (i)
AND parentpartorg = v_tcb_parentpartorg (i)
AND viewname IS NULL;
COMMIT;
EXIT WHEN v_tcb.COUNT < 1000;
END LOOP;
CLOSE cur_tcb;
END;
/
/***************************************************************************************************/

USING NORMAL CURSOR LOOP :
***************************
DECLARE
CURSOR cur_tcb
IS
SELECT tcb.viewname, tcs.ptc_number, tcs.plant, tcs.view_date_created
FROM tcb, tcs
WHERE tcs.ptc_number = tcb.parentpartnumber
AND tcs.plant = tcb.parentpartorg
AND tcb.viewname IS NULL
AND tcs.view_date_created IS NOT NULL;
BEGIN
FOR cur_tcb_rec IN cur_tcb
LOOP
UPDATE tcb
SET viewname = cur_tcb_rec.view_date_created
WHERE parentpartnumber = cur_tcb_rec.ptc_number
AND parentpartorg = cur_tcb_rec.plant
AND viewname IS NULL;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLCODE || ' : ' || SQLERRM);
ROLLBACK;
END;



with regards,
Pavan

[Updated on: Wed, 15 October 2008 08:07]

Report message to a moderator

Re: Using FORALL [message #353886 is a reply to message #353881] Wed, 15 October 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just UPDATE?

Regards
Michel
Re: Using FORALL [message #353887 is a reply to message #353881] Wed, 15 October 2008 08:10 Go to previous messageGo to next message
discoverer
Messages: 30
Registered: January 2007
Member
I can use normal cursor loop for updating, but I want to know why performance is decreased when I use FORALL, when it is supposed to increase.
Re: Using FORALL [message #353889 is a reply to message #353887] Wed, 15 October 2008 08:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why cursor loop and not just one and only one UPDATE statement?

Regards
Michel
Re: Using FORALL [message #353891 is a reply to message #353881] Wed, 15 October 2008 08:16 Go to previous messageGo to next message
discoverer
Messages: 30
Registered: January 2007
Member
Because I am updating a column in one table with the value of a column in another table .


Regards,
Pavan

[Updated on: Wed, 15 October 2008 08:26]

Report message to a moderator

Re: Using FORALL [message #353893 is a reply to message #353891] Wed, 15 October 2008 08:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then? This can be done with one UPDATE.

Regards
Michel
Re: Using FORALL [message #353895 is a reply to message #353881] Wed, 15 October 2008 08:40 Go to previous messageGo to next message
discoverer
Messages: 30
Registered: January 2007
Member
How so ? Can u please show me an example if possible.





Regards,
Pavan
Re: Using FORALL [message #353896 is a reply to message #353895] Wed, 15 October 2008 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 add (deptname varchar2(30));

Table altered.

SQL> update emp2 
  2  set deptname=(select dname from dept where dept.deptno=emp2.deptno);

14 rows updated.

Regards
Michel

[Updated on: Wed, 15 October 2008 08:47]

Report message to a moderator

Re: Using FORALL [message #353897 is a reply to message #353881] Wed, 15 October 2008 08:46 Go to previous messageGo to next message
discoverer
Messages: 30
Registered: January 2007
Member
Thanks a lot. I will try this. My lazy brain couldn't get this idea Razz

Regards,
Pavan




[Updated on: Wed, 15 October 2008 08:46]

Report message to a moderator

Re: Using FORALL [message #353898 is a reply to message #353897] Wed, 15 October 2008 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Other ways:
SQL> merge into emp2
  2  using dept
  3  on (dept.deptno=emp2.deptno)
  4  when matched then update set deptname=dname
  5  /

14 rows merged.

SQL> alter table emp2 add primary key (empno);

Table altered.

SQL> update (select empno, deptname, dname from emp2, dept where dept.deptno=emp2.deptno)
  2  set deptname=dname
  3  /

14 rows updated.

Regards
Michel
Re: Using FORALL [message #353900 is a reply to message #353881] Wed, 15 October 2008 08:51 Go to previous messageGo to next message
discoverer
Messages: 30
Registered: January 2007
Member
Thank u very much Michel.

Regards
Pavan
Re: Using FORALL [message #354121 is a reply to message #353881] Thu, 16 October 2008 08:21 Go to previous message
discoverer
Messages: 30
Registered: January 2007
Member
It Works, processing time got reduced to 7 min.

UPDATE tcb
SET viewname =
(SELECT DISTINCT tcs.viewname
FROM tcs
WHERE tcs.ptc_number = tcb.parentpartnumber
AND tcs.plant = tcb.parentpartorg
AND tcs.view_date_created IS NOT NULL),
view_date_created =
(SELECT DISTINCT tcs.view_date_created
FROM tcs
WHERE tcs.ptc_number = tcb.parentpartnumber
AND tcs.plant = tcb.parentpartorg
AND tcs.view_date_created IS NOT NULL);

Thanks again Michel.



Regards
Pavan

[Updated on: Thu, 16 October 2008 08:44]

Report message to a moderator

Previous Topic: View for total length in bytes for all columns in tables
Next Topic: sending email automatically from oracle
Goto Forum:
  


Current Time: Sun Dec 11 06:20:44 CST 2016

Total time taken to generate the page: 0.08981 seconds