[Web Lesson excerpt]5.2 Demo data Block Consolidation (merge) effect

1 Conceptual Understanding

Due to the frequent DML operations on the table space, some space debris is bound to affect the system efficiency in space allocation. The methods you can use to defragment your disk are rebuild tables, move, shrink, and so on. Another case is that the delete operation does not reclaim the HWM high watermark, while the Oracle Scan table is still scanned from the 1th block of data to HWM, and when a new record is inserted, it is inserted from the HWM, and the space occupied by the record previously marked for deletion is not released. Not only does this affect the performance of our data scans, but it also wastes space usage, and we use the Move\shrink two technology to organize the merged data blocks separately.

2 Environment Preparation

Create a table and index:

3move Recovery Fragment Demo

Analyze the test table:

To view the data block occupancy of a table:

To view the index:

To delete some data:

Then analyze the test table and query the data blocks that are occupied:

It can be found that when we delete a subset of the data, the number of data blocks that are consumed by the table is not changed, which means that the space occupied by the data we deleted is not released.

Now perform the table move:

Now we're going to do an analysis:

As in, the table analysis found after the tables moved ORA-20000 error, because when we do the table movement, the corresponding table index is invalidated, view the index status:

Rebuild the index and view the status:

Now perform the table analysis to see if the table consumes data blocks:

You can see that the data block used by the test table becomes smaller.

4shrink Recovery Fragment Demo

Now we'll use the shrink command to reclaim the table space, which is basically similar to the Move method, and we'll no longer elaborate on direct mapping:

Analyze the data block usage again:

You can see that the number of data that has been consumed by the analyzed table has not changed.

It can be found that when the shrink command is executed, the data block data occupied by the test table is changed from 1050 to the 870,test table Idx_test index is not affected, which means that we perform the shrink command. It is important to note that there is a difference between the move command table space reclamation: When there is an index on a table, when you use the move command to reclaim a tablespace merge block, the index on the corresponding table is invalidated, and the index needs to be rebuilt after you execute the move command, which requires special attention, When you use the Shrink command to reclaim a table space to merge data blocks, the indexes on the corresponding table will not be affected.

[Web Lesson excerpt]5.2 Demo data Block Consolidation (merge) effect