topic Re: ps/sql code for merge/copy/delete in Operating System - HP-UX

ps/sql code for merge/copy/delete

Edgar_8 — Fri, 23 Jan 2004 01:22:48 GMT

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!

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 02:01:10 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 02:12:27 GMT

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!

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

Yogeeraj_1 — Fri, 23 Jan 2004 02:31:27 GMT

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

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 02:36:43 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 03:01:12 GMT

Hi,

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

Thanks in advance!

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 03:25:35 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 03:36:49 GMT

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!

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

Yogeeraj_1 — Fri, 23 Jan 2004 04:19:02 GMT

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

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 04:45:57 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 04:56:49 GMT

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!

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

Yogeeraj_1 — Fri, 23 Jan 2004 05:01:33 GMT

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

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 05:49:39 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 06:13:25 GMT

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!

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 06:30:01 GMT

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

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

Edgar_8 — Fri, 23 Jan 2004 06:48:16 GMT

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!

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 07:00:13 GMT

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

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

Sanjay Kumar Suri — Fri, 23 Jan 2004 07:04:51 GMT

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

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

Yogeeraj_1 — Fri, 23 Jan 2004 08:41:26 GMT

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

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

Brian Crabtree — Fri, 23 Jan 2004 17:29:00 GMT

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