General
cancel
Showing results for 
Search instead for 
Did you mean: 

ps/sql code for merge/copy/delete

Edgar_8
Regular Advisor

ps/sql code for merge/copy/delete

Hi,

We have a DB, with a temp_table that is loaded with data every hour via sqlldr direct parallel method.There
is also a prod_table, with indexes & daily partitions, that needs to be loaded with the data from
the temp_table, and then the coppied/merged temp_table data must be deleted from the temp_table.
Can someone please help with a PL/SQL procedure/package/trigger code that can be used for the extraction/copy/merge/delete of
data from temp_table to prod_table.

Thanks in advance!
32 REPLIES
Sanjay Kumar Suri
Honored Contributor

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

If structure of both the tables is same, then check the following:

begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/

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,

Thanks for the feedback.Will the delete only delete records that were inserted? Is the sql you provided the mist optimal method
or do you have any PL/SQL code which would be better?

Thanks in advance!
Yogeeraj_1
Honored Contributor

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

hi,

the optimal method will depend on the number of records you are loading.

You did not state the database versions etc.

please provide more information.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

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

Hello

This is one of the method.

Delete will delete all the rows from temp_table.

This is what I could get from your original post.

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,

Apologies, the temp_table will have minimum of 10 million records per day. Any advice on most
optimum PL/SQL?

Thanks in advance!
Sanjay Kumar Suri
Honored Contributor

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

1. truncate table xxx; will be faster compare to delete from xxx. It will not generate undo information.

2. Drop all indexes on prod_table before bulk load to hasten the loading.

3. Recreate the index subsequently.

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,

The prod_table cannot have disabled/dropped indexes during the bulk load due to users querying it.
Besides rebuilding/recreating an index on a VLOB table will take hrs in which time users will not be able
to run queries.We require a solution that will ensure fast data loads; fast prod_table loads via copy/merge and also fast querying during
copy/merge? Your assistance is most appreciated!

Thanks in advance!
Yogeeraj_1
Honored Contributor

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

Hi again,

since you are using partitioned tables, this opens up the interesting possibility of:

yd@MYDB.MU> alter table t
2 exchange partition p_0012
3 with table NEW_DATA
4 without validation
5 /

and you could easily swap a new set of data in there at the drop of a hat (and not lose grants, etc)....

You could also turn this table with a single partition into a multi-partitioned table without a dump and reload (or using the dbms redefinition package in 9i)

Also, to rebuild the indexes you can simply use:
ALTER TABLE table_name MODIFY PARTITION part_name REBUILD UNUSABLE LOCAL INDEXES


Hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

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

Another thing which you can try is commit the bulk insert after every load of n rows where n can be 1/10 of number of rows which are loaded.

After this batch insert same rows also need to be deleted from temp_table.

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,

We are looking for a pl/sql code which does the following:
a cursor containing the records selected from the temp_table, after fecthing each row, each row must be
inserted into the prod_table after which the procedure must terminate. Any ideas how to PL/SQL code the above?

Thanks in advance!
Yogeeraj_1
Honored Contributor

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

hi again,

You did not mention your database version.

If you have 9i DB release 2, you can also sync up the flat file with the database table using this single command:

merge into EMP e1
using EXTERNAL_TABLE e2
on ( e2.empno = e1.empno )
when matched then
update set e1.sal = e2.sal
when not matched then
insert ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
values ( e2.empno, e2.ename, e2.job, e2.mgr, e2.hiredate, e2.sal, e2.comm,
e2.deptno )
/
commit;

that'll update the records in the EMP table from the flat file if they exist OR it will insert them.

Doing a direct path load would simply be:
insert /*+ append */ into emp select * from external_table;

Hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Sanjay Kumar Suri
Honored Contributor

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

Check the following PL/SQL (Where f1 is a assumed field in both the tables)

declare
i number := 0;
v_row temp_table%ROWTYPE;
cursor C1 is SELECT * FROM temp_table;
begin
OPEN C1;
LOOP
FETCH C1 INTO v_row;
EXIT WHEN C1%NOTFOUND;
insert into prod_table (f1) values (v_row.f1);
i := i + 1;
if i > 10000 then
commit;
i := 0;
end if;
END LOOP;
CLOSE C1;
delete from temp_table;
end;
/

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,

Thanks for the assistance! If there are 39 columns how would one specify that in your code? And can
your code be included in a procedure?

Thanks in advance!
Sanjay Kumar Suri
Honored Contributor

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

Multiple fields can be inserted like:

insert into prod_table (f1, f2, ...) values (v_row.f1, v_row.f2, ...);

where .... is for more fields.

I am just checking the other part.

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,

The above PL/SQL was saved as a procedure & when executed the estimated duration for a single daily
partition is 40 hrs? Is the code at its optimum?

Thanks in advance!
Sanjay Kumar Suri
Honored Contributor

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

Hi

I don't think so as rows are inserted into prod_table one by one using cursor. And we are loading lot of rows in the cursor which is in memory.

So the better soultion is what I posted first

begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/

Above insert can be changed to following if fields names are different.

insert into prod_table (f1, f2) select f1,f2 from temp_table;

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

My dear

I am closing for the day. I am off for 3 days.

All the best. I learnt a lot while solving this post.

Regards.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Yogeeraj_1
Honored Contributor

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

hi,

Please allow me to add the following...

You can optimize on the "best way" suggested by sks above by breaking the statement into several parts and launch them in parallel.

But this would imply some prior knowledge of the data in the temporary table.

i.e.
begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/

would be:
Script1
begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/

begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/

....
....

begin
insert into prod_table select * from temp_table where ;
delete from temp_table where ;
end;
/
===========================================

where may be:

between and

hope this helps too!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Brian Crabtree
Honored Contributor

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

Edgar,

I have a thought. It would require some changes to your temp table, and it would make changes to your application more difficult, but...

What about adding an additional field into your temp_table (and prod if need be, it would simplify things) that you could put a distinct identifier that would correspond to a load number.

You could generate a controlfile with a constant load number set and load the tables, and then know exactly what you are moving when you run the procedure or load.

Example:

insert into prod_table as select colB,colB from temp_table where loadnum = '1';

delete from temp_table where loadnum = '1';

This would make it so that you don't need to worry about another load infringing on data that you want to delete.

Thanks,

Brian
Sanjay Kumar Suri
Honored Contributor

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

Another way to improve performance is using nologging during bulk load. For example

alter table prod_table nologging;
insert /* +append */ into prod_table select * from temp_table;
alter table prod_table logging;

Once nologging attribute is set on a table, redo entry generation will be suppressed.

sks
A rigid mind is very sure, but often wrong. A flexible mind is generally unsure, but often right.
Yogeeraj_1
Honored Contributor

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

hi,

Concerning Sanjay's post, i comment that "redo entry generation will NOT be suppressed" but rather minimised.

There are indexes on the table and since indexes cannot be "appended" to, they are modified. These modifications must be logged. The redo/undo for the table itself is not generated, the redo/undo for the INDEXES always is.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edgar_8
Regular Advisor

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

Hi,

We have created the below procedure:

BEGIN
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';
commit;
END;
/

This proc works fine & is also included in a job which is scheduled to run every 30 minutes. The prolem
now is that sqlldr, which loads the data in the temp_table continuously, is killed by the procedure
& the below errors are logged to the sqlldr log file:

ORA-00604: error occurred at recursive SQL level 1
ORA-00054: resource busy and acquire with NOWAIT specified

Any ideas how we could resolve these conflicts?

Thanks in advance!
Sanjay Kumar Suri
Honored Contributor

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

I was reading SQL Loader where I come across the folowing:

There are two methods in SQL*loader-

1. Direct Path Load - Other users can't make changes to tables.

2. Conventional Load - Other users can make changes to tables.

How temp_table is populated in your case? I also need to study more on SQL*Loader.

Regards.

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,

We are using direct load method for sqlldr as its more faster that conventional!

Thanks in advance.