http:\/\/community.oracle.com\/thread\/3882414<\/a>) described a fairly extreme example of the \u201cmassive delete\u201d 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 \u201cdrop partition\u201d with virtually no overheads.<\/p>\nIn this particular case the user was (in my opinion) quite lucky \u2013 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.<\/p>\n
Other people may not be so lucky. The type of example I\u2019ve 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 \u201cexchange once then wait for another three years\u201d<\/em> 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.<\/p>\nScenario B<\/h3>\n
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 \u201cjust delete it\u201d<\/em> had been so small that there seemed to be no great point in adopting the slightly more complex (hence risky) option.<\/p>\nThis prompts an interesting question: how big does a delete have to be before it is a \u201cmassive\u201d delete? This person was deleting 25 million rows so it does sound pretty massive but it was \u201conly\u201d 4% of the table \u2013 so maybe it\u2019s 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\u2019s only about 1.5 million rows per PX server so maybe it\u2019s not massive after all (\u2026 and on the other,other, other hand \u2026).<\/p>\n
In fact, regardless of method, the time to complete the task was around 17 minutes 30 seconds \u2013 but it\u2019s worth noting that (a) during that time other users would still be able to use the table if we took the \u201csimple delete\u201d 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)<\/em> \u2013 and there\u2019s always the threat of locks and deadlocks causing a catastrophe, and (c) how often does this 4% delete have to take place \u2013 maybe it\u2019s roughly equivalent to one month of data out of 2 years, so maybe it\u2019s a regular once per month clean-up and no-one minds losing access for 15 minutes for a “drop\/delete\/rebuild”, and there\u2019s always the fringe benefit that most of the indexes are probably going to operate as efficiently as they can after the delete.<\/p>\nConsiderations<\/h3>\n
I hope that the two examples start to give you some idea of how much you have to think about when the term \u201cmassive delete\u201d starts floating around the organization. So before we carry on with \u201chow\u201d let\u2019s try to categorize the cases that might appear and the ideas that need to go with them.<\/p>\n
I think there are three basic patterns<\/strong> <\/em>of deletion that I\u2019ve come across in the past, and two reasons<\/strong><\/em> for deleting. The reasons<\/strong> <\/em>are very simple:<\/p>\n\n- We want to improve performance<\/li>\n
- We want to reclaim space \u2013 the desire might be for space inside the database or a particular tablespace; it might (ultimately) be for disc space outside the database.<\/li>\n<\/ul>\n
The common patterns<\/strong> <\/em>are:<\/p>\n\n- We want to delete table data based on the time it arrived<\/li>\n
- We want to delete table data based on the time its processing was \u201ccompleted\u201d<\/li>\n
- We want to remove a category of data from a table (which may ultimately mean we want to create two tables, or partition \u2013 by list, perhaps \u2013 a non-partitioned table).<\/li>\n<\/ul>\n
As soon as we propose the reasons we invite some critical questions \u2013 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?<\/p>\n
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<\/strong><\/em> 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<\/strong><\/em> of the index.<\/p>\nI\u2019ve suggested three major patterns of deletion, based largely on the degree to which they pose a performance threat. It\u2019s easiest to think about these patterns if you assume that you are about to do your big delete for the first time \u2013 sometimes, though, the threat doesn\u2019t materialize until after you\u2019ve gone through the deletion cycle a few times.<\/p>\n
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 \u2013 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\u2019ve 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 \u2013 105,000; the pattern in your data distribution won\u2019t change although the absolute position in the table will have changed ends.<\/p>\n
If you delete based on \u201cprocessing completed\u201d date the initial deletion pattern is likely to be different \u2013 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\u2019ve deleted from won\u2019t allow their space to be re-used until the next time you do a big delete, anyway). Without reference to a real application it\u2019s a little hard to imagine exactly why anyone\u2019s data might show this type of \u201cdecaying\u201d 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.<\/p>\n
The \u201cloans\u201d application might give us a good example of the last pattern \u2013 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\u2019ve 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\u2019re now inserting a few rows per block across the entire width of table for the next nine months with people complaining that \u201cperformance went really bad in 2016\u201d.<\/p>\n
Indexing<\/h3>\n
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\u2019re deleting all the rows from a (relatively) small number of adjacent blocks of the table we need to consider what\u2019s going to happen to each of the indexes on that table. A very compact table<\/strong><\/em> delete might lead to a very scattered index<\/strong><\/em> delete that (a) might take a long time because of all the random I\/O \u2013 reads (by the session) and writes (by the database writer) \u2013 and (b) might not give us any subsequent space or performance benefit.<\/p>\nConsider deleting (say) 1st April 2001 from a \u201cstock price\u201d<\/em> table: all the rows will have arrived together so we may empty several hundred consecutive blocks in the table \u2013 which should be efficient \u2013 and if we have an index on (quote_date, stock_code)<\/em> we\u2019ll empty a couple of hundred consecutive blocks in the index, which won\u2019t produce an excessive amount of I\/O if that\u2019s the index we use to drive the delete; but if we have an index on (stock_code, quote_date)<\/em> \u2013 and it\u2019s very likely that we would \u2013 we\u2019ll 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 \u201cdb file sequential read\u201d<\/em> waits; execution plans (in Oracle) don\u2019t tell us anything about the overheads of index maintenance so it\u2019s 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.)<\/p>\nThe impact of index maintenance on large deletes is so significant \u2013 and can have such long lasting consequences \u2013 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<\/strong><\/em> after the delete.<\/p>\nSummary<\/h3>\n
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?<\/p>\n
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.<\/p>\n
In the next installment I\u2019ll go into some of the technical issues relating to a \u201cmassive\u201d delete. \u00a0<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u201cmassive delete\u201d. In this article, then, I\u2019m going to lay the groundwork for how we ought to think around t…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48400,5722,48441],"coauthors":[],"class_list":["post-73135","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-deletes","tag-indexing","tag-massive-deletes"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73135","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73135"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73135\/revisions"}],"predecessor-version":[{"id":91631,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73135\/revisions\/91631"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73135"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}