Oracle - Delete & Then Insert

nvalia · Post by **nvalia** » Mon Sep 25, 2006 8:59 am

Hi,

We have a requirement where we need to clean the table prior to insert.
Example if src has 100 records.

The idea is, if for some reason the job fails/aborts/hangs (may be due to DB problem or any other reason), when we RE RUN the job it should clear the previous loaded records (if any) and then insert the 100 records.

I tried using 2 ORA Enterprise stage (DB Oracle 10g), one for delete and other for Insert and ordering the Delete link from the transformer as the first one to be executed.
It works fine for a few hundred records, but once we have a 1000 records or more it hangs.
I am executing them sequentially and not in parallel.

Thanks,
NV.

avi21st · Post by **avi21st** » Mon Sep 25, 2006 11:25 am

What is specificly happening- I guess it is the performance of delete - but you are talking of 1000 records which is really less in volume.

Doing a delete is ofcourse slow as it works on row by row. If you have audit columns - I mean a Create Date and Modified Date in all your target tables then you can delete based on date so that you don't have to match on any other keys.

People also have tables partitioned on date or monthyear depending on their business. They cantruncate the ecent partition and load.

Provide more info

nvalia · Post by **nvalia** » Mon Sep 25, 2006 11:41 am

HI,

This table can be populated by other processes too on the same day...we want to delete only those rows that were inserted by our process.. SO using only the dates for delete without the keys will not help.

Has it got something to do with table locking in Oracle..Row Level vs Table level?

NV

ArndW · Post by **ArndW** » Tue Sep 26, 2006 1:24 am

If you change your commit frequency to 1 so that each row is immediately committed and the error goes away then you have answered the locking question positively.

nvalia · Post by **nvalia** » Tue Sep 26, 2006 11:00 am

Although doing this may slow down the job, after doing that too, it doesn't work.It still hangs up.
Any thing else I can check for?

chulett · Post by **chulett** » Wed Sep 27, 2006 7:27 am

Try using a single stage for your two Oracle links. This is a valid solution for a Server job and wondering if it will solve the same transactional issues in a PX job as well.

If not, you should get your DBA involved and have them trace what is going on during the run of the job.

nvalia · Post by **nvalia** » Wed Sep 27, 2006 1:06 pm

Yes, even I had implemented this in a Server job.
Here I am using a single transformer stage having 2 output links, one for delete & other for Insert and executed in that order. But No Luck.

Anyways,I have now split my job in 2 parts..one will Delete and then another will Insert using the DS created from delete job. This will ensure 2 seperate transactions and hence no chances of a Deadlock.
This is working fine now.

Thanks All.

chulett · Post by **chulett** » Wed Sep 27, 2006 2:56 pm

nvalia wrote:Yes, even I had implemented this in a Server job. Here I am using a single transformer stage having 2 output links, one for delete & other for Insert and executed in that order. But No Luck.

Actually, I meant using one Oracle stage connected to both links from your Transformer. This allows them to run the in same 'transaction' and generally avoids issues like you seem to be having. When you say 'hangs' that could also mean 'runs really slow', especially if you are doing singleton deletes on every row. We typically constrain the delete link to only fire on the first row and to do all the deleting it needs to do at once right then - if at all possible.

Of course, breaking the job up into two pieces is a perfectly valid solution as well.