Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE vs DELETE...INSERT (Oracle10g)
UPDATE vs DELETE...INSERT [message #326361] Wed, 11 June 2008 03:42 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
In general, does a single UPDATE take more time than a DELETE and INSERT, the selection criteria for UPDATE and DELETTE are the same ?
Re: UPDATE vs DELETE...INSERT [message #326362 is a reply to message #326361] Wed, 11 June 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ UPDATE will take less time
2/ DELETE+INSERT is NOT the same thing as UPDATE unless you work in serializable mode (and may fail) or lock the table

Regards
Michel
Re: UPDATE vs DELETE...INSERT [message #326369 is a reply to message #326361] Wed, 11 June 2008 04:48 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member


If the server Capacity is Low they were Deletion is fast and Updatation of Record may be slow. If the consign table contain lot of records also set slow in operation
Re: UPDATE vs DELETE...INSERT [message #326377 is a reply to message #326369] Wed, 11 June 2008 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why would you think that a DELETE would be quicker than an UPDATE? Both of then have to lock the records, both of them create undo and redo data.


I don't understand your second sentence at all.
Re: UPDATE vs DELETE...INSERT [message #326392 is a reply to message #326362] Wed, 11 June 2008 06:00 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Wed, 11 June 2008 03:47
1/ UPDATE will take less time
2/ DELETE+INSERT is NOT the same thing as UPDATE unless you work in serializable mode (and may fail) or lock the table

Regards
Michel





What is serializable mode? Can you please explain why UPDATE is faster?
To my knowledge, Insert is faster than Update as it does not go for check ( if no contraint are imposed).
In deletion Log is maintained.

Am I wrong here? Need your suggesion here.
Thanks & Regards,
Oli
Re: UPDATE vs DELETE...INSERT [message #326396 is a reply to message #326392] Wed, 11 June 2008 06:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A serializable transaction is one where every SELECT or DML statement that you execute sees that data as it was at the start of the transaction.
Eg if you run the same SELECT statement several times while other users are commiting changes that affect the rows that the SELECT fetches, then the results of the selects will be different.
In a serializable transaction, all the executions will return the same results.

Insert, Update and Delete all have to maintain the Undo_Log otherwise they could not be rolled back.

Similarly, the changes that all three make are recorded in the Redo_Log to allow for database backup and recovery.
Re: UPDATE vs DELETE...INSERT [message #326399 is a reply to message #326396] Wed, 11 June 2008 06:23 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@JRowbottom

Thanks for making me understand
Re: UPDATE vs DELETE...INSERT [message #326400 is a reply to message #326392] Wed, 11 June 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
To my knowledge, Insert is faster than Update

Did you make test for this?
Post the result.

You don't do INSERT, you do DELETE+INSERT, why 2 statements should be faster than 1?

Quote:
What is serializable mode?

This is explained in documentation and it is a standard in databases. Have a look to wikipedia for a first sight.

Quote:
In deletion Log is maintained.

I don't know what you meant with this.
All DML generate redo and undo data.

Regards
Michel
Re: UPDATE vs DELETE...INSERT [message #326403 is a reply to message #326400] Wed, 11 June 2008 06:29 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel for pointing out my mistake...
Michel Cadot wrote on Wed, 11 June 2008 06:24


You don't do INSERT, you do DELETE+INSERT



I didnt get you here!
Re: UPDATE vs DELETE...INSERT [message #326408 is a reply to message #326403] Wed, 11 June 2008 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Olivia wrote on Wed, 11 June 2008 13:29
Thanks Michel for pointing out my mistake...
Michel Cadot wrote on Wed, 11 June 2008 06:24


You don't do INSERT, you do DELETE+INSERT



I didnt get you here!

You compared UPDATE and INSERT, but the actual comparison is UPDATE and DELETE+INSERT.

Regards
Michel

Re: UPDATE vs DELETE...INSERT [message #326414 is a reply to message #326408] Wed, 11 June 2008 06:37 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

Quote:

You don't do INSERT, you do DELETE+INSERT



I am not clear here. What did you mean when you said "You don't do INSERT, you do DELETE+INSERT". You meant to say while we do insert ,actually we do DELETE + INSERT?? How?
Re: UPDATE vs DELETE...INSERT [message #326421 is a reply to message #326396] Wed, 11 June 2008 06:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If in doubt, do some tests.

I ran the attached timing script, and got these results
DEL/INS No index: 8304
Update No Index: 9484
DEL/INS w Index: 541
Update w Index: 213
DEL/INS w unq Index: 357
Update w unq Index: 167


Interestingly, on the un-indexed table, the Delete/Insert is actually 13% faster, but loses out substantially in the presence of an index.

I'm at a bit of a loss to explain why the Update is slower in the first case.

drop table timing_test;

create table timing_test (col_1 number, col_2 number);

insert into timing_test (select level,0 from dual connect by level<= 10000);

declare
  v_time   pls_integer;
  v_loop   pls_integer := 20000;
  v_idx    pls_integer;
begin
  dbms_random.seed(1);
  v_time := dbms_utility.get_time;

  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,10000);
    DELETE timing_test where col_1 = v_idx;
    
    insert into timing_test values (v_idx,1);

  end loop;
  dbms_output.put_line('DEL/INS No index: '||to_char(dbms_utility.get_time-v_time));

  v_time := dbms_utility.get_time;
  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,10000);
    update timing_test set col_2 = 1 where col_1 = v_idx;

  end loop;
  dbms_output.put_line('Update No Index: '||to_char(dbms_utility.get_time-v_time));
  
  execute immediate 'create index tt_idx on timing_test(col_1)';
  
  v_time := dbms_utility.get_time;

  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,100);
    DELETE timing_test where col_1 = v_idx;
    
    insert into timing_test values (v_idx,1);

  end loop;
  dbms_output.put_line('DEL/INS w Index: '||to_char(dbms_utility.get_time-v_time));

  v_time := dbms_utility.get_time;
  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,100);
    update timing_test set col_2 = 1 where col_1 = v_idx;

  end loop;
  dbms_output.put_line('Update w Index: '||to_char(dbms_utility.get_time-v_time));  
  
  execute immediate 'drop index tt_idx';
  execute immediate 'create unique index tt_idx on timing_test(col_1)';
  
  v_time := dbms_utility.get_time;

  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,100);
    DELETE timing_test where col_1 = v_idx;
    
    insert into timing_test values (v_idx,1);

  end loop;
  dbms_output.put_line('DEL/INS w unq Index: '||to_char(dbms_utility.get_time-v_time));

  v_time := dbms_utility.get_time;
  for i in 1..v_loop loop
    v_idx := dbms_random.value(1,100);
    update timing_test set col_2 = 1 where col_1 = v_idx;

  end loop;
  dbms_output.put_line('Update w unq Index: '||to_char(dbms_utility.get_time-v_time));  
end;
/
Re: UPDATE vs DELETE...INSERT [message #326577 is a reply to message #326362] Thu, 12 June 2008 00:57 Go to previous message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for the answer Michel.
Previous Topic: Generating sequence numbers for Duplicate records
Next Topic: Update using join of tables
Goto Forum:
  


Current Time: Sat Dec 03 22:24:12 CST 2016

Total time taken to generate the page: 0.04518 seconds