Refreshing tables when the users are accessing it

Question

We have a few Aggregate tables in our DataWarehouse that are truncated and refreshed Fully for each ETL run. It takes about 10-15 mins for some of the tables to completely load. We have 24-7 usage and the users are impacted for those 10-15 mins when the tables are being refreshed.

What options are there to keep a static version of the table while the new data is being loaded and switch the tables after the load is complete without interrupting user queries. We use Oracle 12cC Database.

Any guidance on this is greatly appreciated.

Thanks,

Vaishali

Gianni Ceresa · Answer

Forgetting the context of OBIA etc. and just focusing on a database point of view there are few techniques to reduce the down time of a table while loading.

For example partition exchange, where you load the new data in another temporary table and then when done you switch the partition of your main table with the one in the new temp table and it's like if the load just took seconds (or even less) instead of minutes.

Alternatives would just be to load on a temporary table and then drop the real one and rename the temporary one, or things along that line.

I would guess your DBA could best advise on which technique will work better for your DB based on the table itself. Then you will need to identify how to add that login into your ETL to make it works (and as far as I know most ETL support or can support these things).