Batch - Update vs Insert then Delete There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 are Oracle 12c. Now, the issue is there are  about a million and a half records in database2 and based on statistics, everytime the ETL will be run, about 45% of the records in database2 is likely to be updated.

Now we're thinking how long could it take to update about 675,000 records every time the ETL is run. We would want it to be execute at a certain time window and not go on running beyond that. Would it be more efficient to insert then delete instead? rather than update them all? We would also like the implementation to be future proof - although there is db purging for database2 (delete those more than 3 years), there is a possibility for data to grow at a high rate in the next few months.
If the data is purely read-only once it arrives in the target database (and you have lots of disk space available), here's an alternative you might consider. 

-- Two tables with the SAME structure 
create table table1_a ( ...  
create table table1_b ( ...  
-- No Application permissions on these tables

create view table1_read  as select * from table1_a ; 
create view table1_write as select * from table1_b ; 

grant select                         on table1_read to application_role ; 
grant delete, insert, select, update on table1_write to etl_role ; 


The application that consumes the loaded Data always reads from table1_read. 

Your application doing the ETL always writes to table1_write. 

Apart from the background work in the database churning all that data across, there is no impact on the Application ...  until, at the end of a successful load, you invoke a stored Procedure that works out which View is looking at which Table and then switches the definitions of the two Views. 

create or replace view table1_read  as select * from table1_b ; 
create or replace view table1_write as select * from table1_a ; 


This allows you to use really fast, database-level utilities to trash and reload one of the tables while the Application continues to read the other and then with minimal, if any, impact on the Application, makes the new data available and frees up the other table ready for loading next time.  

OK, you are doubling your data storage requirement, having two copies of the data lying around but if, for any reason you suspect the newly loaded Data is wrong, you can [very] quickly revert to "yesterday's data" by switching the Views over again.  No re-running the ETL load process to get back to a stable, working system. 

Sure; it's a sledgehammer approach, but is has the advantage of simplicity and has proven remarkably resilient.  That Procedure is the only "fiddly" bit but it can be coded in such a way that it only has to be written and tested once and reused for any number of tables treated in this way.