Oracle Upserts and Primary Keys/Unique Indexes

[whenry6000]

Does Oracle Enterprise stage need a primary key on the Oracle table itself to perform an upsert properly? or just a unique index?? Or neither?

I am attempting to load data into a table and using the upsert method. However, if I disable the key on the table manually, it appears to load duplicates. I am assuming that the Oracle Enterprise stage uses the key to check, but wondering if a unique index does the same thing.

As an alternative, i could split out inserts versus updates, but the only way to do that seems to be doing a lookup against the target table. Some of them are 20 million plus rows. Is it practical to do a lookup against 20 million rows??

When dealing with large tables, how are people resolving the issue of loading large volumes of data where you might need to do an upsert?

Thanks for any information...

[Pagadrai]

Hi,

As far as I can say, It is always better to match the primary key in the database and the keys defined in Oracle stage.

The keys that we mention in the Oracle Enterprise stage will form the basis for an insert / update action done by datastage.
If the keys dosent match, you might end up having duplicate records / wrong updates etc.

Keys in Oracle Enterprise stage dosent check for duplicate records being inserted to the table. If you do not want duplicates in the DB table, this has to be handled separately in the job or you can use a Unique constraint in the table.

[ShaneMuir]

To perform an upsert, you only need to specify the key columns in the Oracle stage itself. These can be different to the key values in the Oracle table (though when loading Oracle will enforce its own rules). A primary index will do the same thing but generally these are dropped and then rebuilt on loading. Duplicates could still be loaded and the index would not be rebuilt.

There are a number of options on the Oracle stage around disabling constriants and indexes. I suggest you look at these (sorry I don't have access handy at the moment) as I am sure I am over simplifying it.

[nagarjuna]

Shane you have mentioned "Oracle stage around disabling constriants and indexes" .I dont this these option will be applicable to upsert option .

[chulett]

Does Oracle Enterprise stage need a primary key on the Oracle table itself to perform an upsert properly? or just a unique index?? Or neither?

An upsert requires a unique index to function correctly, be that PK or otherwise. Actually, let me amend this somewhat.

Since the first action must fail for the second to fire, you need a unique index for the "Insert else Update" method to work as violating that is what fires the update. For the "Update else Insert" way of things, the 'failure' that needs to happen is for the update to affect zero rows to trigger the insert attempt. That secondary insert could still fail due to PK or UI violations, of course, but a unique index plays no role in triggering this method.

[keshav0307]

Does Oracle Enterprise stage need a primary key on the Oracle table itself to perform an upsert properly? or just a unique index?? Or neither?

either

[chulett]

I like my answer more better.