Re: Updating a table

vikasa_at_att.com (Vikas Agnihotri) wrote (in <slrn6k93c9.kf0.vikasa_at_8080b0da.ims.att.com>)...

|
| Have a file containing key columns + additive facts. A table in the
| database needs to be updated using this file. If the row exists based on
| the key, update the row otherwise insert it.
|
| Which of the following methods do you think will be the fastest and why?
|

Another way:

1) Load temporary table.
2) Run update query where master row exists in temporary table..
3) Run insert query where master row does not exist in temporary

Cost:
1 Load of temporary table.
2 Two full table scans of temporary table

Oracle can read tables using full table scans quickly. It may well be quicker to this than one scan in PL/SQL. Also the Insert and Update can be run simultaneously as they are using two separate sets of rows.
Very simple to code and maintain.

graham Received on Tue Apr 28 1998 - 02:22:23 CDT