0

Need some help here.

I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by reporting team to generate reports. This is done to minimize the downtime for report generation. This way the reports are always accessing the latest data.

Target table B is a List partition table, partitioned on Client ID. ETL team loads data for each client in the respective partition, in Table A.

I tried doing it with Exchange partition: exchange partition mechanism to swap the segments of A and partitioned table B. But couldn’t do it as both tables are List partitioned and Oracle doesn’t like that. I created partitions so as to avoid creating multiple tables (TableA_ClientId) for each Client.

My other option: whenever data is loaded into target table A, rename the table B as table Temp, table B as table A and table A as table Temp.

Can you please suggest a better approach.

Improve this question
0

1 Answer 1

Reset to default
0

Answer left in a comment by Andrew Sayer:

You can create a non-partitioned staging table with the right structure. You then partition exchange from Table_A to the staging table, then from the staging table to Table_B. What are you going to do with the old data now in Table_A?

Improve this answer
1
  • ETL does a Truncate load so data in Table_A will be truncated in next load. With this approach I will need to repeat this process A -> Stg -> B for each Client ID partition. Right> Or I'm missing something
    – Manish Mishra
    Jun 24, 2021 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.