Oracle Delete operation hides secrets you may not know

description of a phenomenon

Delete is a common operation in the Oracle database, especially in the automated test, the initialization environment, the pre-preparation are unavoidable additions and deletions, but after a while, may encounter the table space is not enough such error phenomenon, this can not help but wonder, Clearly before inserting the data will be deleted, the total amount of data does not show a significant change in magnitude, why the table occupied space is secretly increased?

two phenomenon analysis

the reason for the above phenomenon is that The Delete operation does not release the space occupied. Before explaining why, it is helpful to understand the concept of high watermark in Oracle, which is useful for understanding the phenomenon caused by delete operations.

the so-called High water level ( HWM), popularly speaking is a mark, used to record how many blocks of datahave beenallocated to the table, you can take the reservoir's history of the highest water level analogy, when using the delete operation, the data is deleted, But this high-water mark does not degrade, just as the reservoir's historical highest water level is not lowered by the release of water. Thus, in principle, without external intervention, the high water mark value will only increase and will not degrade.

three real- combat simulations reproduce the phenomenon

According to the above phenomenon description and analysis, next, I will use concrete examples to simulate the phenomenon, so that you can more intuitive understanding.

Section 1, create a test table testing , specific fields do not need to be concerned, as long as you know the initial storage space of 100M,:

2, after creation, we look at the space occupied by the following data sheet:

where the table needs to be parsed before the query, using the command:ANALYZE TABLE Test ESTIMATE STATISTICS;the query statements are:SELECTblocks, empty_blocks, Num_rows fromUser_tablesWHEREtable_name =' TEST ';

Notice the result of the above three fields:  blocks=0;  empty_blocks=13312; Num_rows=0, that is,the current table occupies a block of 0, the default 1 block = 8kb, the pre-allocated block is 13312 , the number of rows is 0.

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

3, write a block of statements, loop through the Insert , the test table again analysis, query, the result is as follows:

can see, Occupy blocks=222,num_rows=1000, logical, inserting the data of the bar, taking up space.

4, using the delete statement to remove the data, the test table again analysis, query, the result is as follows:

It can be clearly seen that after the data has been deleted, Num_rows=0 , but BLOCKS is not set to 0, which is still considered a part of the data block.

As a result, the phenomenon mentioned in the beginning of this article, with the continuous insertion and deletion of data, BLOCKS will also continue to expand, as soon as the delete operation, the amount of data in the table may be small, but the table occupies no less storage space.

four workaround

for A delete operation caused by the space is not released, or, more formally, how to reduce the high watermark, there are many ways, such asshrink space;move tablespace CREATE TABLE XXX as SELECT * from XXX rebuild table. Before using these methods, our principles are:

If you can truncate the truncatedirectly , the operation resets the high watermark,BLOCKS will be set to 0,num_rows 0; otherwise, priority is given to using shrink space, which does not require rebuilding the index.

then the upper section4step, we useShrink Spacelower the high watermark, freeing up space, where, usingShrink Spacebefore the command, you need to firstAlter Table Test Enable Row Movement;turn on row movement, analyze the table again, query, the result is as follows:

as you can see, at this point BLOCKS has been set to 0 , but, careful you may also find that empty_blocks is not the initial 13312, but at this time ,this shows Shrink space Not only frees up spaces below the high watermark, it also frees up the application space, i.e. the high watermark is operated on top and bottom, which is also different from move,truncate , which can only release space below the high watermark.

Five shrink space common operation commands

common commands for Shrink space are as follows:

six potential impact of Delete operations

based on the above analysis, The potential impact of the delete operation is as follows:

1. A full-table scan typically reads all data blocks that belong to the table until the HWM is marked, even if there is no data in the table; (resulting in slower queries)

2. insert operation using the append keyword, even if the HWM below has an idle database block, insert the use of more than HWM data blocks; (cause HWM to increase automatically)

Seven summary

through the description and analysis of the above phenomena, as the insert continues to operate, the high watermark increases with increasing, although the data is dropped, the high watermark does not drop, resulting in no release of the space occupied by the table. Therefore, in practical applications, if possible, to use truncate, and the operation is efficient and fast, otherwise consider the impact of the delete operation, using the appropriate method to organize the space.

Oracle Delete operation hides secrets you may not know