archivebackuporacleoracle-11g-r2
Our company has a big data server in which large data is stored, like images, video, audio, etc. Our company wants a process running with the new year, such that in 2020 the previous year's 2019 data will be automatically archived and removed tables the live tables. In the future, we'll take a backup of the archived data.
I am new in this field. Which Mechanism should I use for this purpose?
This is a pretty good case for Distributed Partitioned Views.
It would be fairly easy to implement, especially if the archives have the same schema.
I have found the Data Loading Performance Guide, which you reference, to be an extremely valuable resource. However, it is also not 100% comprehensive, and I suspect that the grid is complex enough already that the author did not add a column Table Partitioning to break out differences in behavior depending on whether the table receiving the inserts is partitioned. As we will see later, the fact that table is already partitioned appears to inhibit minimal logging.
Based upon the recommendations in the Data Loading Performance Guide (including the "Bulk Loading a Partitioned Table" section) as well as extensive experience loading partitioned tables with tens of billions of rows, here is the approach I would recommend:
The differences when compared to your original approach:
TABLOCK one month at a time, using partition switching to place the data into the partitioned table.DELETE to clear away old table will be fully logged. Perhaps you can either TRUNCATE or drop the table and create a new archive table.In order to compare approaches in a reasonable amount of time on my machine, I used a 100MM row test data set that I generated and that follows your schema.
As you can see from the results below, there is a large performance boost and reduction in log writes by loading data into a heap using the TABLOCK hint. There is an additional benefit if this is done one partition at a time. It's also worth noting that the one-partition-at-a-time method can easily be parallelized further if you run multiple partitions at once. Depending on your hardware, that might yield a nice boost; we typically load at least four partitions at once on server-class hardware.
Here is the full test script.
All of these results depend on your hardware to some degree. However, my tests were conducted on a standard quad-core laptop with spinning disk drive. It's likely that the data loads should be much faster if you are using a decent server that does not have a lot of other load at the time you are conducting this process.
For example, I ran the recommended approach on an actual dev server (Dell R720) and saw a reduction to 76 seconds (from 156 seconds on my laptop). Interestingly, the original approach of inserting into a partitioned table did not experience the same improvement and still took just over 12 minutes on the dev server. Presumably this is because this pattern yields a serial execution plan, and a single processor on my laptop can match a single processor on the dev server.
Best Answer
If you used the optional feature table partitioning. You could easily drop the previous years partitions without affecting the current year. If it were me I would partition by month and sub partition by hour. You might want to keep more than the current year online. Hence partition by month would allow you to keep a rolling 11 months if you wanted and drop the oldest partition every month.