Upsert Statement

[bala_135]

Hi All,

Requirement:A Delta extract will change to full extract at a specfic time period[Eg:say every 3 months].It will have the same version of the records in my datawarehouse only during that time period.

My approach:I am changing the write mode from insert to upsert in my oracle connector stage .Writing an update query that will only update the audit columns as to updating all the columns.

Any downside in this approach.If I update all the columns does it have any impact in terms of query performance. I know this is a database question but I am having a doubt with this approach as I have always filtered the unchanged records through ETL.This is the first time I using this approach due to time constraints.

Note:-The where clause columns are indexed.

Thanks in advance.

Regards,
Bala.

[ray.wurlod]

It won't make a whole lot of difference, because (unless very, very wide) the entire row is read and written when an UPDATE is performed. Indeed, it is the page containing the row that is read and written.

[chulett]

My approach:I am changing the write mode from insert to upsert in my oracle connector stage .Writing an update query that will only update the audit columns as to updating all the columns.

Is this even valid? In older Oracle stages it would not be, hence my question about the Connector.

[priyadarshikunal]

May be if you keep it as upsert (insert than update) and pass were clause as 1=2 or 1=1 depending on what you want to do.

May be just a different way to express.

[chulett]

I am assuming they are saying they want to bind all of the columns in the stage to the insert DML but only a few to the update DML. From what I recall that wouldn't fly in the older Oracle stages as they want all columns bound all the time. ODBC didn't care but native OCI did.

[bala_135]

Hi All,

Thanks for all your inputs. Apologies for the delayed reply. There is an option in the oracle connector stage "Insert new rows only". I am not going to perform any updates.

Regards,
Bala