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.
Upsert Statement
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Upsert Statement
Is this even valid? In older Oracle stages it would not be, hence my question about the Connector.bala_135 wrote: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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Re: Upsert Statement
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.
May be just a different way to express.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers