UPDATE versus INSERT INTO

[karacharlie]

does an UPDATE on a table take more time than an insert into an empty table with the same definition ?
emample:
UPDATE tab_1
set field_1 = 1 ...
;
versus: (tab_1 has same definition like tab_2)
INSERT INTO tab_2 (field_1, ...)
SELECT 1, ...
FROM tab_1
;

thanks
charlie

 

[SantaMufasa]

Charlie,

Your comparative scenarios presently are an "apples-to-oranges" comparison. They are not doing the same things. From a stictly functional (apple-to-apples) perspective, INSERTs involve less overhead since they generate virtually no rollback data and INSERTs do not involve WHERE-clause evaluation.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:18 (31Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:18 (31Dec04) Mountain Time

 

[johnherman]

Generally speaking, and I do mean generally, you can estimate time based on the following algorithm.

SELECT or READ = 1 "unit"
INSERT, WRITE, or DELETE = 2 "units"
UPDATE or REWRITE = 3 "units"

Thus a rewrite is about three times as expensive as a read while an insert or write is only about twice as expensive.

NOTE: This is a general rule. Indexes, cursors, triggers, constraints, database design, database internals, etc can and do affect the rule.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[stefanhei]

In addition to Dave's and johnherman's excelent explanations: if talking about a 1 time task and when the amount of data involved is huge, a common method is to create a table instead of inserting:

CREATE TABLE tab_2
(field_1 number(2),...)
AS
SELECT 1,...
  FROM tab_1;

Stefan