Thanks for the question, Ashish.
Asked: April 28, 2020 - 8:29 am UTC
Last updated: April 29, 2020 - 3:36 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi,
We have a table which is history of around 13 years and growing. This table is partitioned by list (2 values) and sub-partitioned again by list w.r.t to reporting dates.Current size of the table is 5 TB.
The data gets loaded every month(always on end of Month).
There are few business requirement where they need to update the table of the complete history. Post these updates there is slowness seen and always fragmented. Please note we always do either export and import of the whole table( which takes 5 days) or the alter table move sub-partition which takes around 40 hours.
I would like to know is there are better way to update this history table avoid these performance issue. From the DBA perspective we are creating separate tablespaces w.r.t to the year.
I know that the updates fragments the table and this is causing the performance issues. I would like to have better approach w.r.t to update these records and less performance impact.
Regards
Ashish
and Connor said...
There are few business requirement where they need to update the table of the complete history
By this do mean actually update each row in that 5TB ? That sounds like a design issue, because one of the fundamental maxims of data warehouses is that you load, and ideally never change history. That is not just for performance reasons, but also the fact that it renders any reporting you've done in the past off the original information invalid. Data Warehouses often have temporal designs to ensure that we never change the past - we only adjust for it in the future.
Anyway, the fastest way to manipulate data in Oracle is to create it. That is always faster than deleting it or changing it.
So if I had to change 5TB of data, I would load a *new* set of 5TB of data (with the changes I want) and then use exchange partition to swap the old with the new.
But changing *all* of the history on a table that keeps growing...is eventually going to cause more and more problems
Is this answer out of date? If it is, please let us know via a Comment