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)