ps/sql code for merge/copy/delete

[Michael Schulte zur Sur]

Hi Edgar,

I am wondering. Is it possible, that while you copy data from temp to prod, new rows are inserted into temp? If so, you may miss rows, which are inserted after the insert begins.

Michael

[Edgar_8]

Hi Michael,

Yes you are correct, while we copy data there are rows being inserted/appended into temp_table
via sqlldr. Do you think that we might be deleting records that were not copied over to the
prod_table? If so, how do you propose that we prevent this?

Thanks in advance!

[Sanjay Kumar Suri]

While direct path load is in progress, other transactions (such as update on prod_table) can't make changes to the tables that are being loaded (temp_table).

The only exception to this rule is when several parallel direct load sessions are used concurrently.

So loading in temp_table should get over before transfer to prod_table.

Refer
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch09.htm#1008227

for more on Parallel Direct Path Load

sks

[Sanjay Kumar Suri]

Hello

You have to synchronize the SQL Loader job and update procedure in such a way that one is running at a time.

For example: Immediately after loading in temp_table, start the procedure to upload data in prod_table.

sks

[Michael Schulte zur Sur]

Hi,

insert into prod_table select * from temp_table where loaded = 'N';
commit;
update temp_table set loaded = 'Y' where loaded = 'N';
delete from temp_table where loaded = 'Y';

Oracle has a feature called read consistency, meaning, when you start the select from only the commited rows are considered. The update/delete however may have another set of rows, when they start. You could delete rows, which are not in your prod table yet.
My idea would be to add a timestamp field to the temp table. First you would get the latest time stamp, which you use for insert and delete. That way you would have a consistent set of rows.

Michael

[Sanjay Kumar Suri]

Hello Michael

This issue (read consistency) will not occur if we put commit in the last:

insert into prod_table select * from temp_table where loaded = 'N';
update temp_table set loaded = 'Y' where loaded = 'N';
delete from temp_table where loaded = 'Y';
commit;

I still think that direct loading (sql*loader) will have conflict with simultanous execution of any other DML.

sks

[Edgar_8]

Hi Sanjay/Michael,

We will test the "commit" after the update/delete transactions to see what impact it has on records being
loaded during the procedure run operation. Thanks to all for the help thus far, we appreciate it!

[Michael Schulte zur Sur]

Hi,

if you precede the insert with a:
set transaction read write
then all subsequent commands til the next commit will refer to the same scn for read consistency.

Michael