- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: ps/sql code for merge/copy/delete
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 05:22 PM
тАО01-22-2004 05:22 PM
ps/sql code for merge/copy/delete
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 06:01 PM
тАО01-22-2004 06:01 PM
Re: ps/sql code for merge/copy/delete
begin
insert into prod_table select * from temp_table;
delete from temp_table;
end;
/
sks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 06:12 PM
тАО01-22-2004 06:12 PM
Re: ps/sql code for merge/copy/delete
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 06:31 PM
тАО01-22-2004 06:31 PM
Re: ps/sql code for merge/copy/delete
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 06:36 PM
тАО01-22-2004 06:36 PM
Re: ps/sql code for merge/copy/delete
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 07:01 PM
тАО01-22-2004 07:01 PM
Re: ps/sql code for merge/copy/delete
Apologies, the temp_table will have minimum of 10 million records per day. Any advice on most
optimum PL/SQL?
Thanks in advance!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 07:25 PM
тАО01-22-2004 07:25 PM
Re: ps/sql code for merge/copy/delete
2. Drop all indexes on prod_table before bulk load to hasten the loading.
3. Recreate the index subsequently.
sks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 07:36 PM
тАО01-22-2004 07:36 PM
Re: ps/sql code for merge/copy/delete
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 08:19 PM
тАО01-22-2004 08:19 PM
Re: ps/sql code for merge/copy/delete
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-22-2004 08:45 PM
тАО01-22-2004 08:45 PM
Re: ps/sql code for merge/copy/delete
After this batch insert same rows also need to be deleted from temp_table.
sks