Operating System - HP-UX
1828370 Members
3017 Online
109976 Solutions
New Discussion

Re: ps/sql code for merge/copy/delete

 
Michael Schulte zur Sur
Honored Contributor

Re: ps/sql code for merge/copy/delete

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
Regular Advisor

Re: ps/sql code for merge/copy/delete

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
Honored Contributor

Re: ps/sql code for merge/copy/delete

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

A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Sanjay Kumar Suri
Honored Contributor

Re: ps/sql code for merge/copy/delete

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Michael Schulte zur Sur
Honored Contributor

Re: ps/sql code for merge/copy/delete

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
Honored Contributor

Re: ps/sql code for merge/copy/delete

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
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Edgar_8
Regular Advisor

Re: ps/sql code for merge/copy/delete

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
Honored Contributor

Re: ps/sql code for merge/copy/delete

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