In a recent article about dropping columns from a table I left hanging the question of what you might do after you have dropped the column, because dropping a column is really nothing more than a special case of the “massive delete”. In this article, then, I’m going to lay the groundwork for how we ought to think around the problems of “massive deletes” and their aftermath.

Overview

Before you can address the options and mechanics of the massive delete you have to come up with good answers to a few strategic (long-term) and tactical (short-term) questions.

At the strategic level you have questions like: Why are you doing the delete, what do you hope to gain from it, what’s the on-going strategy (if any) once you’ve achieved your initial aim? What evidence do you have that it’s going to be worth the effort (human and machine)? Have you thought carefully about how the exercise may introduce new problems even if it fixes an old one?

On a tactical level you can ask the questions that ultimately dictate the detail of the mechanisms you use to get the job done: What resources are available? Are you allowed a lengthy interrupt to service or just a very brief one – or none at all? If the application has to keep running while the job is taking place can it run with a reduced set of functions or a reduced level of performance? Do you know enough at present about your system to do something safe? Have you checked which recent features or enhancements in Oracle might help you to do the job more safely (and possibly more quickly)?

Let’s take a couple of online conversations I’ve been involved in recently as a basis for highlighting a few ideas:

Scenario A

A recent question on the OTN database forum (http://community.oracle.com/thread/3882414) described a fairly extreme example of the “massive delete” requirement. The user had a simple heap table sized at 4TB holding three years of data and wanted to reduce this to 15 days of history partitioned by day. Possibly the commonest requirement that pushes people to the massive delete is the desire to get rid of a chunk of history. One of the best strategies, of course, is to have designed the system with this target in mind in the first place so that the delete turns into a simple “drop partition” with virtually no overheads.

In this particular case the user was (in my opinion) quite lucky – they wanted to get rid of most of the data and retain only a tiny fraction of it. It will take a little time to plan and test all the surrounding details (referential integrity, indexing etc) but all that needs to be done (in principle) is to create a suitable partitioned table with range (or interval) partitioning, exchange this table into it as the bottom partition and then partition daily from there onwards. Wait 16 days then drop the bottom partition to get rid of the last 3 years of data.

Other people may not be so lucky. The type of example I’ve seen more frequently tends to have several years of data in a monolithic table and a requirement to keep (and query) two or three years of data partitioned by week or month. The idea of “exchange once then wait for another three years” is not entirely desirable but the overhead of deleting several years, or copying out the recent years that are to be kept, is equally undesirable.

Scenario B

A question that arrived in my in-tray a little while ago came from someone asking about strategies for deleting large volumes of data because their previous experience had suggested that the fastest option for a massive delete was to drop all indexes before the delete and rebuild them all afterwards. They had recently tested a case, though, where the difference in time between this approach and “just delete it” had been so small that there seemed to be no great point in adopting the slightly more complex (hence risky) option.

This prompts an interesting question: how big does a delete have to be before it is a “massive” delete? This person was deleting 25 million rows so it does sound pretty massive but it was “only” 4% of the table – so maybe it’s not so massive (relatively speaking); moreover the table was partitioned, which reduces the threat somewhat. On the other hand there was (at least) one global unique index, which is a bit nasty. The machine, however, was able to run this task in parallel at degree 16 so in absolute terms that’s only about 1.5 million rows per PX server so maybe it’s not massive after all (… and on the other,other, other hand …).

In fact, regardless of method, the time to complete the task was around 17 minutes 30 seconds – but it’s worth noting that (a) during that time other users would still be able to use the table if we took the “simple delete” strategy, but (b) the delete might take a lot longer because of the concurrent use and user activities might be slower because of the contention and read-consistency requirement (side note: would there be any benefit in deleting from one partition at a time in a particular order) – and there’s always the threat of locks and deadlocks causing a catastrophe, and (c) how often does this 4% delete have to take place – maybe it’s roughly equivalent to one month of data out of 2 years, so maybe it’s a regular once per month clean-up and no-one minds losing access for 15 minutes for a “drop/delete/rebuild”, and there’s always the fringe benefit that most of the indexes are probably going to operate as efficiently as they can after the delete.

Considerations

I hope that the two examples start to give you some idea of how much you have to think about when the term “massive delete” starts floating around the organization. So before we carry on with “how” let’s try to categorize the cases that might appear and the ideas that need to go with them.

I think there are three basic patterns of deletion that I’ve come across in the past, and two reasons for deleting. The reasons are very simple:

• We want to improve performance
• We want to reclaim space – the desire might be for space inside the database or a particular tablespace; it might (ultimately) be for disc space outside the database.

The common patterns are:

• We want to delete table data based on the time it arrived
• We want to delete table data based on the time its processing was “completed”
• We want to remove a category of data from a table (which may ultimately mean we want to create two tables, or partition – by list, perhaps – a non-partitioned table).

As soon as we propose the reasons we invite some critical questions – how will deleting data improve performance, could we get improved efficiency by other means (such as improved indexing)? Will the space we produce by deleting data immediately be usable or do we have to take further steps? What are the side effects of the deletion and what are the side effects of any further steps we may have to take? Do we have a realistic platform on which we can check our predictions of down-time, practice the task, and test for unpredictable side effects?

Understanding patterns, of course, is very important – and something that is frequently overlooked in all areas of how the Oracle database is used. When you delete data you create space in table blocks and index leaf blocks and that space might be re-used as new data appears; but the way in which the free space is spread across the table may mean that the physical distribution of the new data is very different from the original distribution pattern that the rest of the data currently follows, and this means that over time (a) queries may become less efficient because of the change in the pattern, and (b) the optimizer might decide that a particular index is no longer a good choice because the change in the data distribution pattern has resulted in an associated change in the clustering_factor of the index.

I’ve suggested three major patterns of deletion, based largely on the degree to which they pose a performance threat. It’s easiest to think about these patterns if you assume that you are about to do your big delete for the first time – sometimes, though, the threat doesn’t materialize until after you’ve gone through the deletion cycle a few times.

If you delete based on the original arrival date of your data you are likely to leave a lot of completely empty blocks at the start (first few extents) of the table – this means that the newly arriving data is going to go into a well clustered set of blocks at the start of the table instead of a well-clustered set of (new) blocks at the end of the table. To make that concrete, imagine you have a table of 100,000 blocks and you’ve just deleted all the data from the first 5,000 blocks; the next couple of hundred thousand rows you insert will go to blocks 1 – 5,000 rather than to block 100,001 – 105,000; the pattern in your data distribution won’t change although the absolute position in the table will have changed ends.

If you delete based on “processing completed” date the initial deletion pattern is likely to be different – perhaps the first 1,000 blocks become virtually empty, the next 1,000 blocks drop to 20% usage, the next 2,000 blocks to 40% usage, and the next 4,000 to 70% usage. As time passes your new data is now going to be spread over far more blocks than it used to be (and maybe some of those blocks you’ve deleted from won’t allow their space to be re-used until the next time you do a big delete, anyway). Without reference to a real application it’s a little hard to imagine exactly why anyone’s data might show this type of “decaying” pattern as large deletes take place – but you could perhaps think of an application that was capturing loan agreements with a 1,2,3, or 5 year lifetime.

The “loans” application might give us a good example of the last pattern – deleting an entire category of data. We might decide for some reason that we want to create a separate table for the 5 year loans that have grown to make up an important fraction of the business – so we have to delete them from the current loans table. The pattern here, of course, is that we’ve just deleted a volume somewhere between 10% and 30% of every single block in the table. We might find that none of those blocks appears in the free space map, or we may find that we’re now inserting a few rows per block across the entire width of table for the next nine months with people complaining that “performance went really bad in 2016”.

Indexing

Of course, when we look at patterns of data we also ought to think about patterns (and side effects) in indexes. Even if one of the scenarios means we can delete the data from the table fairly efficiently because we’re deleting all the rows from a (relatively) small number of adjacent blocks of the table we need to consider what’s going to happen to each of the indexes on that table. A very compact table delete might lead to a very scattered index delete that (a) might take a long time because of all the random I/O – reads (by the session) and writes (by the database writer) – and (b) might not give us any subsequent space or performance benefit.

Consider deleting (say) 1st April 2001 from a “stock price” table: all the rows will have arrived together so we may empty several hundred consecutive blocks in the table – which should be efficient – and if we have an index on (quote_date, stock_code) we’ll empty a couple of hundred consecutive blocks in the index, which won’t produce an excessive amount of I/O if that’s the index we use to drive the delete; but if we have an index on (stock_code, quote_date) – and it’s very likely that we would – we’ll have to visit a few thousand index leaf blocks to delete one index entry from each! The delete could be very slow because it has to do a huge amount of random I/O. One of the commonest complaints about bulk inserts and bulk deletes on the OTN database forum used to be the amount of time spent on “db file sequential read” waits; execution plans (in Oracle) don’t tell us anything about the overheads of index maintenance so it’s very easy to forget that a big delete can do a lot of slow, random I/O. (It’s interesting to note that plans in SQL Server do tell you which indexes you have to maintain as you delete.)

The impact of index maintenance on large deletes is so significant – and can have such long lasting consequences – that it really merits some careful thought. In fact we may have to design a strategy that treats the indexes on a single table differently depending on the definition and usage of the each index. For a given table we may drop (or mark unusable) and rebuild some indexes while leaving some indexes in place then doing a rebuild or coalesce after the delete.

Summary

A large delete is not a trivial operation and should not be undertaken without some thought. Why do we want to delete a large volume of data and do we know how we can demonstrate after the event that we have achieved that aim? What methods can we use to minimize the impact of performing the delete and what might we have to do after the delete is complete to reach our ultimate goal?

The effectiveness, and extra workload, will be dictated by the pattern of the data that we want to delete, first in the table itself then (perhaps even more importantly) in the indexes.

In the next installment I’ll go into some of the technical issues relating to a “massive” delete.