Upsert in Oracle

sethuraman · Post by **sethuraman** » Tue Dec 21, 2010 8:49 am

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.

chulett · Post by **chulett** » Tue Dec 21, 2010 9:00 am

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.

chowdhury99 · Post by **chowdhury99** » Tue Dec 21, 2010 9:12 am

Chulett is right. 1st create index on ROW_ID column of the table. After that if still it is slow, then add Index HINTS.

Thanks.

sethuraman · Post by **sethuraman** » Tue Dec 21, 2010 9:20 am

Hi,

The ROW_ID column is indexed but I have not used Index HINTS. Is it must for all Upsert jobs?

chowdhury99 · Post by **chowdhury99** » Tue Dec 21, 2010 9:23 am

Until running the compute statistics on the table stat is not updated. So, you need to add HINTS on your update statement.

Thanks.

chulett · Post by **chulett** » Tue Dec 21, 2010 10:37 am

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.