Shulou(Shulou.com)05/31 Report--

This article will explain in detail why undo occupies twice as much space as the original table when deleting a large table, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Overview

In Oracle, undo is mirrored before saving records. I understand that if the undo generated by delete from t; should be about the same size as the t-table, but the test results are very poor, the amount generated by undo is basically twice the size of the t-table. I don't know why, am I wrong? Let's take a look at this strange phenomenon.

1. Delete for 8 hours

two。 Original table size

It can be found that the original table is only 16.5G, and the data to be deleted is 9G.

3. View undo block

I forgot to take a screenshot here, but there are more than 3 million blocks. The corresponding undo takes up more than 30 gigabytes, which is far larger than the size of the original table.

Why does undo take up so much space?

In principle, UNDO tablespaces serve four purposes:

Roll back the transaction

Consistent reading

Transaction recovery

Flashback query

Ask the elder Yang for some information:

For the rollback transaction, it saves the previous mirror of the modified value, note that it is not the modified data block, or the mirror of the entire row of records.

In addition to considering the table size, there are also the total size of the index on the table, whether there are triggers, materialization attempt logs, and so on. In addition, see if the database-level supplemental log is open.

Undo is mirrored before recording things are modified, and the front mirror of delete is the data stored in the table. Of course, some may cause the front mirror to be larger than the original data in the table, such as compression, a non-empty default value that exists after 11g.

In addition, there must be some additional costs for undo records, such as information such as rowid,scn, which can be prominent if the rows in the table are small.

It is indeed difficult to know exactly how much extra information is, but through this experiment, we can at least understand the capacity deleted by a delete operation, and the capacity that UNDO needs to occupy in order to save the previous image, which is one of the reasons why it is not recommended to delete too much data in a delete operation.

In short, the case of a large amount of data in delete must be carried out in batches, preferring more time and less risk to avoid database stutters caused by accidental rollback.

About why undo takes up twice as much space as the original table when deleting a large table, I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.