Which is faster? Inserting 1 Million rows of data or Updating 1 Million rows of data?

Which is faster? Inserting 1 Million rows of data or Updating 1 Million rows of data?

• It depends
• Updating
• Inserting
• Google it! 

 

EXPLANATION

INSERT is always faster because UPDATE necessarily requires a scan(whether aided by indexes or not) to find the individual row(s) to alter. By contrast, the database engine already has the next locations in each table allocated and identified for INSERT actionsand maintains this information in the control files.
INSERT is also faster than DELETE and INSERT as a substitute for UPDATE for two reasons: not only is the database required to _find_the rows to DELETE, but it must maintain the UNDO for BOTH the DELETE and INSERT actions in the redo logs. A DELETE will take just as long as an UPDATE by itself. Adding the additional INSERT afterwards only further increases the time differential. 

SQL> --create table juva as select * from dba_objects
SQL> truncate table juva
Table truncated.
Elapsed: 00:00:01.09
SQL> insert into juva select * from dba_objects
71238 rows created.
Elapsed: 00:00:02.12
SQL> commit
Commit complete.
Elapsed: 00:00:00.17
SQL> update juva set object_type = 'unknown'
71238 rows updated.
Elapsed: 00:00:05.97
SQL> commit
Commit complete.
Elapsed: 00:00:00.20