What is the best way to delete some data
Total number of rows:1,000,000,000 Three months of rows:100,000,000
We need to delete the data for the last three months, what is the best way
What you are trying to achieve? I mean... Do you want or recover disk space, or you just need to query the latest months of data without any filtering?
I am asking because the detete statement right now isn't going to recover space, it going to add a bitmaps and metadata for deleted rows; something is on the works, but it hasn't materialized yet and I don't know when it will be ready.
To recover space and get the best performance would be re-create a new table with the needed data (and naturally drop the old one), but if the space isn't a concern, maybe a logical delete (with update) could be an effective short term strategy.
I have to do some benchmarking to understand the impact of both those approach on caches (with the first one you are going to have cpu/Gpu caches invalidated)
Please sign in to leave a comment.