Oracle Enterprise Stage - Insert statement using Upsert

[synsog]

Hi,

We have an insert statement and we are using the Write Method as Upsert in Oracle Enterprise Stage.

My Question is - What is the difference between using the upsert mode - "User Defined Update Only" mode and writing the Insert statement here against using the "User Defined Update & Insert" mode where we basically do not update anything by giving something like 1=2 in the where clause and only use the Insert part.

Is there a performance impact by using the Update mode to actually insert against using the insert mode for insert, as both work but which is the correct & efficient way?

Thanks

[felixyong]

In term of performance pure Insert or pure Update is always faster then Upsert.

If due to certain biz rules, we've to use Upsert then we need to choose wisely on whether Update then Insert OR Insert then Update as there's performance differences as well depend on whether you've more Insert/Update data.

[BI-RMA]

The reason a pure insert is faster, is that for upsert the database will evaluate for every row wether a key-value is already present in the database to be able to decide which statement to execute for the specific row. This makes it necessary to set the array-size - the number of records passed to the database at a time - in DataStage to 1.

When running only one kind of query DataStage can send lots of rows to the database at once reducing overhead.

Pure inserts will still maintain index-structures on the target-database. Therefore - when using inserts only - "Load" with disabled constraints and Index-Rebuild at the end is often faster than using "User defined Update only" for inserts.