Brief introduction of Oracle High Water level High Water Mark (HWM)

1. What is a high water level

High water level is the boundary line between used and unused space in Oracle, and it can also be understood as the dividing line between data text and non-data text, but the data file here refers to the data file that has stored data, not just the data file with data in the data table.

two。 Effect of High Water level on Retrieval

Suppose we have a table with 2 million rows of data stored in several extents, if we delete 1 million pieces of data. Although the data in the data area is gone, the data space occupied by the Oracle remains the same as the original 2 million pieces of data. When we execute a query in the database, Oracle still traverses all the blocks, even if there is no data in those blocks. From this point of view, high water levels can be understood as blocks or areas of data that have been used.

At least one of the blocks at high water level has data, which may also be deleted. Because Oracle knows that there is no data in the data area on the high water level, it only reads the high water mark when reading the data. It is this principle that when the table data in the database increases to a certain amount of data, moving the data in the table to a new history table and emptying the data in the current table will not improve the retrieval efficiency of the current data table. Because the high water level of the data has not changed.

If most of the data in the database is deleted, the Shrink command should be used to lower the high water level in order to improve the query efficiency of the data. Of course, using database partitioning tables is another solution.