Shulou(Shulou.com)06/01 Report--

Description of a phenomenon

Delete is a common operation in oracle database, especially in automated testing, initialization environment, pre-preparation are inevitable to add and delete operations, but after a period of time, may encounter insufficient table space such as error phenomenon, this can not help but wonder, obviously there will be deleted before inserting data, the total amount of data does not show a significant change in magnitude, why the table space is secretly increasing?

Analysis of two phenomena

The reason for this is that the Delete operation does not release the occupied space. Before explaining the reason, we should understand the concept of high water level line in oracle, which is helpful to understand this phenomenon caused by delete operation.

The so-called high water level (HWM), in popular terms, is a mark, which is used to record how many data blocks (Block) have been allocated to the table. You can take the historical highest water level of the reservoir as an analogy. Although the data is deleted after using the delete operation, the mark of this high water level has not been lowered, just like the historical highest water level of the reservoir will not be lowered because the water has been released. Therefore, in principle, under the condition of no external intervention, the marker value of this high water level will only increase, not decrease.

Three phenomena of recurrence of actual combat simulation

According to the above phenomenon description and analysis, next, I will use specific examples to simulate the phenomenon, so that you can more intuitively understand.

First, create a test table test. You don't need to care about the specific fields, as long as you know that the initial storage space is 100m, as shown in the figure:

Second, after the creation is complete, we look at the space occupied by the following data table, as shown in the figure:

Before querying, the table needs to be analyzed. The command is: ANALYZE TABLE test ESTIMATE STATISTICS; query statement: SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name = 'TEST'

Notice the results of the above three fields: BLOCKS=0; EMPTY_BLOCKS=13312; NUM_ROWS=0, that is, the number of blocks occupied by the current table is 0, the default is 1 BLOCK = 8kb, the pre-allocated block is 13312, and the number of rows is 0.

Everything is fine, the newly created table, there is no data, of course, the number of rows is 0, the number of blocks is 0.

Third, write a statement block, insert 1000 statements in a loop, and analyze and query the test table again. The results are as follows:

As you can see from the picture, it takes up BLOCKS=222,NUM_ROWS=1000, logically, inserts 1000 pieces of data, and takes up space.

Fourth, delete 1000 pieces of data using the delete statement, and analyze and query the test table again, and the result is as follows:

It is clear from the image above that after the data is deleted, the NUM_ROWS=0 is changed, but the BLOCKS is not set to 0, that is, this part of the data block is still considered to be occupied.

Therefore, there is the phenomenon mentioned at the beginning of this article, with the continuous insertion and deletion of data, BLOCKS will continue to expand, as soon as possible after delete operation, the amount of data in the table may be very small, but the storage space occupied by the table has not been reduced.

Four solutions

There are many ways to reduce the high water mark due to the non-release of space caused by the delete operation, or, more formally, how to lower the high water mark, such as shrink space;move tablespace;create table xxx as select * from xxx to rebuild the table. Before using these methods, our principles are:

If you can truncate, directly truncate, the operation will reset the high water mark, and BLOCKS will be set to 0, and the BLOCKS will be set to 0; otherwise, shrink space will be preferred, and the method does not require index reconstruction.

Then, in step 4 above, we use shrink space to lower the high water mark and free up space. Before using the shrink space command, we need to alter table test enable row movement; to enable row movement, and then analyze and query the table. The results are as follows:

As can be seen from the figure, BLOCKS has been set to 0 at this time. However, if you are careful, you may also find that EMPTY_BLOCKS is no longer the initial 13312, but 40 at this time, indicating that shrink space will release not only the space below the high watermark, but also the space applied for, that is, operations are performed above and below the high watermark, which is also different from move and truncate, which can only release the space below the high watermark.

Five shrink space common operation commands

Common commands for Shrink space are as follows:

Potential impact of six Delete operations

Based on the above analysis, the potential impact of delete operations is as follows:

1. A full table scan usually reads all blocks of data that belong to the table up to the HWM tag, even if there is no data in the table; (causes the query to slow down)

two。 Use the append keyword when inserting, even if there are free database blocks below HWM, use data blocks above HWM when inserting; (causes HWM to increase automatically)

Seven summaries

Through the above phenomenon description and analysis, with the continuous operation of insert, the high water mark is also increasing, although the delete data, but the high water mark did not decline, resulting in the space occupied by the meter is not released. Therefore, in practical applications, if possible, try to use truncate, and the operation is efficient and fast; otherwise, consider the legacy of delete operations and use appropriate methods to organize the space.

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.