Operating System - HP-UX
1748019 Members
4433 Online
108757 Solutions
New Discussion юеВ

Re: 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 32
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.