Introduction to Oracle High Water Mark (HWM)

Introduction to Oracle High Water Mark (HWM)

1. What is high water level?

High water level is the boundary between Oracle and unused space. It can also be understood as the boundary between data files and between non-data files, however, the data files here refer to the data files that have stored the data, rather than the data files that only contain data in the data table.

2. Effects of high water level on Search

Suppose we have a table with 2 million rows of data stored in Several partitions. If we delete 1 million rows of data. Although the data in the data zone is gone, the data space in the zone occupied by Oracle remains unchanged, which is the same as the data space occupied by the original 2 million data entries. When we perform a query in the database, Oracle will still traverse all data blocks, even if there is no data in these data blocks. From this point of view, high water level can be understood as used data blocks or data areas.

At least one data block under a high water level has data, and of course the data may be deleted. Since Oracle knows that there is no data in the data zone on the high water level, it reads data only to the high water level line. It is exactly this principle that when the table data in the database increases to a certain amount of data, if you move the data in the table to a new historical table and clear the data in the current table, does not improve the efficiency of searching the current data table. Because the high level of data has not changed.

If most of the data in the database is deleted, use the Shrink command to reduce the high water level to improve the data query efficiency. Of course, using database partition tables is another solution.

Oracle high water level line and full table Scan

HWM: High Water Mark

Www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates the link address: