Home » SQL & PL/SQL » SQL & PL/SQL » Fastest method for one to one update (Oracle 11g)
Fastest method for one to one update [message #584196] Thu, 09 May 2013 17:45 Go to next message
pritish.oracleapps
Messages: 2
Registered: May 2013
Location: Hyderabad
Junior Member

Hello everyone,

I am new to this forum.

We have a requirement to update one column as given in the below mentioned pl/sql block.
The challenge is to update the a large volume of data every day.
Generally this kind of one to one update will takes long time (45 min approxmately)

We need a solution that will be able to update 10 million records in few minutes.

begin
for i in (select t3.event_id, t1.header_id from
table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id
)
loop
update table_1 set event_id = i.event_id
where header_id =i.header_id;
end loop;
commit;
end;

Please suggest the best possible way to achieve this.

Thanks
Pritish
Re: Fastest method for one to one update [message #584197 is a reply to message #584196] Thu, 09 May 2013 17:49 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

no PL/SQL is required.
implement as single simple UPDATE statement.
Re: Fastest method for one to one update [message #584198 is a reply to message #584197] Thu, 09 May 2013 18:16 Go to previous messageGo to next message
pritish.oracleapps
Messages: 2
Registered: May 2013
Location: Hyderabad
Junior Member

Hi BlackSwan

We have already tried with update statement and merge update aswell, merge update is bit faster but both takes long time.
We need to optimize it to minimum possible time for about 10 m record per day.
Re: Fastest method for one to one update [message #584199 is a reply to message #584198] Thu, 09 May 2013 19:07 Go to previous messageGo to next message
BlackSwan
Messages: 22901
Registered: January 2009
Senior Member
are statistics current for table & indexes?

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof


how many total rows in the table?
about how many rows get UPDATED each day?
Re: Fastest method for one to one update [message #584206 is a reply to message #584199] Fri, 10 May 2013 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59409
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... In addition, note that no PL/SQL code will be faster than a (correct) single SQL statement.

Regards
Michel
Re: Fastest method for one to one update [message #584273 is a reply to message #584206] Fri, 10 May 2013 15:20 Go to previous message
Bill B
Messages: 1116
Registered: December 2004
Senior Member
update table_1 a
set a.event_id =
(select t3.event_id
from table_2 t2, table_3 t3
where a.header_id=t2.header_id and t2.entity_id = t3.entity_id)
where a.header_id in
(select t1.header_id 
from table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id);

[Updated on: Fri, 10 May 2013 15:21]

Report message to a moderator

Previous Topic: convert data to table/inline view
Next Topic: Continuos job
Goto Forum:
  


Current Time: Tue Oct 21 13:22:32 CDT 2014

Total time taken to generate the page: 0.10081 seconds