Implementing delete and insert on oracle enterprise stage

prasadcp · Post by **prasadcp** » Fri Mar 21, 2008 9:27 pm

I need to implement Delete-and-Insert into an oracle table.I know that there is an alternative- 'upsert' - available in the enterprise edition to achieve this.But I am still curious to find out if we can implement delete & insert; since upserts are running slow on my table.
To implement delete-insert; I tried with using two streams in a job from a transformer : one for deleting rows and the other to insert rows using 'load' option.But I'm facing errors while using 'load' due to triggers defined on the table.

Could somebody please guide me.

Thanks

kumar_s · Post by **kumar_s** » Fri Mar 21, 2008 11:46 pm

Hi Prasad,
Welcome Aboard :D
Pls specify what kind of error you are getting.
And more over, you cant delete the records based on certain key and Insert few records based on the same set of keys in the same job.

prasadcp · Post by **prasadcp** » Sat Mar 22, 2008 12:43 am

kumar_s wrote:Hi Prasad,
Welcome Aboard :D
Pls specify what kind of error you are getting.
And more over, you cant delete the records based on certain key and Insert few records based on the same set of keys in the same job.

Thanks, Kumar :D

I'm getting the error as : "SQL*Loader-937: parallel load requested and <table_name> has enabled triggers or constraints"
because, table has a trigger defined on it.While searching on the forums, i found a topic which mentioned that we can overcome this error by using open & close commands.But it was also mentioned there that "these commands try to run it on each processing node and possibly get duplicate or conflicting enable/disable requests".So i want to avoid the load option.

Regarding the delete and inserts, if both operations can not be implemented in the same job then my story ends here!

BTW, Is there any alternate way to implement delete-n-insert - apart from an upsert??

chulett · Post by **chulett** » Sat Mar 22, 2008 6:37 am

prasadcp wrote:BTW, Is there any alternate way to implement delete-n-insert - apart from an upsert??

Sure, two separate operations - first perform all of the deletes and then all of the inserts.

kumar_s · Post by **kumar_s** » Sat Mar 22, 2008 8:09 am

Or one after another with seperated commit in a singe user defined query.
Btw, Open command would execute before the Load Write and Close would execute after the Load write.

Sudhindra_ps · Post by **Sudhindra_ps** » Sat Mar 22, 2008 3:01 pm

Hi,

You could make use of two Oracle enterprise stages in this scenario. Where in one should do the insert operation while the other does only delete operation. These stages should get input links from Transformer stage. In Transformer stage you can specify "link ordering" with "Sequential partitioning" based on your priority of DML statements execution.

Thanks & regards
Sudhindra P S