ps/sql code for merge/copy/delete

[Edgar_8]

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!

[Sanjay Kumar Suri]

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

[Edgar_8]

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]

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

[Sanjay Kumar Suri]

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

[Edgar_8]

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]

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

[Edgar_8]

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]

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

[Sanjay Kumar Suri]

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

[Edgar_8]

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]

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

[Sanjay Kumar Suri]

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

[Edgar_8]

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]

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

[Edgar_8]

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]

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

[Sanjay Kumar Suri]

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

[Yogeeraj_1]

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

[Brian Crabtree]

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]

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

[Yogeeraj_1]

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

[Edgar_8]

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]

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

[Edgar_8]

Hi,

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

Thanks in advance.