Hi,
I need to perform upsert in a table using User defined SQL.
My design is:
Dataset----> CPY ----> Oracle Enterprice.
For example my update query is:
UPDATE Tablename set columnname = ORCHESTRATE.columnname where ROW_ID= ORCHESTRATE.ROW_ID
I need to update for some 100000 records. This takes very long time i.e 2 records per second.
Can I get any solution for this?
Note: The insert, and other loading jobs are running fast except this upsert.
Upsert in Oracle
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Tue Jan 12, 2010 10:34 pm
- Location: Chennai
Upsert in Oracle
Thanks & Regards,
Sethuraman Rajendran,
SE - ISDC,
Renault-Nissan Technology and Business Centre India Private Limited,
Chennai – 603002,
Sethuraman.Rajendran@rntbci.com,
+91-9994304880 | + 91-44-67482128.
Sethuraman Rajendran,
SE - ISDC,
Renault-Nissan Technology and Business Centre India Private Limited,
Chennai – 603002,
Sethuraman.Rajendran@rntbci.com,
+91-9994304880 | + 91-44-67482128.
First question that comes to mind - is your "ROW_ID" column indexed? Sounds like a full table scan to me, meaning it either doesn't have one or it isn't being used. For the later, involve your DBA and get an explain plan to determine why, typically it turns out to be a 'stats' issue from what I've seen.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 43
- Joined: Thu May 29, 2008 8:41 pm
-
- Participant
- Posts: 8
- Joined: Tue Jan 12, 2010 10:34 pm
- Location: Chennai
Hi,
The ROW_ID column is indexed but I have not used Index HINTS. Is it must for all Upsert jobs?
The ROW_ID column is indexed but I have not used Index HINTS. Is it must for all Upsert jobs?
Thanks & Regards,
Sethuraman Rajendran,
SE - ISDC,
Renault-Nissan Technology and Business Centre India Private Limited,
Chennai – 603002,
Sethuraman.Rajendran@rntbci.com,
+91-9994304880 | + 91-44-67482128.
Sethuraman Rajendran,
SE - ISDC,
Renault-Nissan Technology and Business Centre India Private Limited,
Chennai – 603002,
Sethuraman.Rajendran@rntbci.com,
+91-9994304880 | + 91-44-67482128.
-
- Participant
- Posts: 43
- Joined: Thu May 29, 2008 8:41 pm
Technically, you should never "need" hints. As noted, talk to your DBA and have them help you track down the issue. If they don't see anything on their side, you might want to consider involving your official support provider as I seem to recall that it may be an artifact of using those "dual actions" sql statements.
For a true "upsert" in Oracle, you really should be using the MERGE function, something else to talk to your DBA about if you are unfamiliar with it.
For a true "upsert" in Oracle, you really should be using the MERGE function, something else to talk to your DBA about if you are unfamiliar with it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers