Home » SQL & PL/SQL » SQL & PL/SQL » Why update takes time much more than insert
Why update takes time much more than insert [message #193662] Mon, 18 September 2006 16:42 Go to next message
kokkoy
Messages: 1
Registered: September 2006
Location: UK
Junior Member
If anybody know the reason, please answer.

I have update and insert commands as shown below. When I execute them, the insert command takes time about 9 seconds whereas the update command take time about 18 seconds. Thus I wonder that why their time are so different because I execute other insert and delete commands but their times are not much different.

Insert command:

select distinct A.AuthorID bulk collect into l_array
from Author A, Library L
where L.Publication.Year >= '2000'
and L.Publication.Year <= '2001'
and A.PubID = L.Publication.PubID;

forall i in l_array.first..l_array.last
insert into table(select A.Telephone from Author A
where A.AuthorID in l_array(i))
values('Home', '9999999');


Update command:

select distinct A.AuthorID bulk collect into l_array
from Author A, Library L
where L.Publication.Year >= '2000'
and L.Publication.Year <= '2001'
and A.PubID = L.Publication.PubID;

forall i in l_array.first..l_array.last
update table(select A.Telephone from Author A
where A.AuthorID in l_array(i))
set Location = 'Office', TelNo = '9999999' ;
Re: Why update takes time much more than insert [message #193672 is a reply to message #193662] Mon, 18 September 2006 20:44 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trace your session and then run the trace file through TK*Prof. It will tell you exactly what is happening in each SQL.

Insructions for SQL*Trace and TK*Prof can be found in the Oracle Performance Tuning Manual.

Ross Leishman
Previous Topic: creating a stored procedure..cursor question
Next Topic: When to create an index and use hints
Goto Forum:
  


Current Time: Tue Dec 06 04:29:04 CST 2016

Total time taken to generate the page: 0.26901 seconds