Best way to replenish the data in a table
We have a table in one database that needs to be copied nightly in another database. This table is over 1.5 million rows. The current methodology is to truncate the table and then, using a database link, inserts the rows from the first database into the second database. We have placed this table in a nologging tablespace and use the /*+ APPEND */ hint to eliminate the generation of redo information.
The issue is that this takes 1.3 minutes to perform this task. If someone tries to access this table during the 1.3 minutes, there are 0 rows in the select. We perform this at 3:00 a.m.. The application developers are saying that this is not acceptable. I have tried to use a delete from table statement to remove the rows, which allows for availability to data, but the performance degradation (10 minutes to perform the task, a select statement takes minutes, instead of a couple of seconds) during the delete/insert plus the generation of redo/archive files (from the delete) way