Oracle High watermark

In the storage of Oracle data, storage space can be imagined as a reservoir, the data is imagined as water in the reservoir. The position of the water in the reservoir is a line called the watermark, in Oracle, this line is called the High watermark (High-warter mark, HWM). When the database table was just established, because there is no data, so this time the watermark is empty, that is, HWM is the lowest value. When the data is inserted, the high watermark will go up, but there is also a feature here, that is, if you delete the data using a DELETE statement, the data is deleted, but the high watermark is not lowered, or you just deleted the data before the high water level. In other words, this high watermark will only rise in daily additions and deletions and will not fall.

Let's talk about the features of the SELECT statement in Oracle. The SELECT statement scans the data in the table one at a time, but how much data storage blocks are scanned, this does not mean how much data is in the database, and Oracle scans such large chunks of data, but Oracle scans the blocks below the high watermark. Now imagine that if you had just created an empty table and you did a select operation, because the high watermark HWM in the lowest 0 position, no data block would have to be scanned and the scan time would be very short. And if you first insert 10 million data at this time, then delete the 10 million data with the DELETE statement. Since 10 million data is inserted, the high watermark at this time is 10 million data here. When the 10 million data is deleted, the high watermark is still 10 million data, because the DELETE statement does not affect the high watermark line. This time again with the SELECT statement to scan, although this time there is no data in the table, but because the scan is based on the high watermark, so you need to 10 million of data storage space to be scanned once, that is, the time required for this scan and scan 10 million data will take as much time. So sometimes people always say, how my table is not a few data, but still so slow, this time in fact, the mystery is the high water mark here.

There is no way to let the high water mark down, in fact, there is a relatively simple way, that is, using the TRUNCATE statement to delete data. when using the TRUNCATE statement to delete data from a table, it is similar to re-establishing the table, not only deleting the data, but also restoring the HWM to 0. So if you need to empty the table, use the TRUNCATE statement to delete the table when it is possible to use the TRUNCATE statement to delete the data, especially the temporary storage table that has a potentially large amount of data.

In manual segment space management (Manual Segment spaces Management), there is only one HWM in the segment, but the automatic segment space management that is added in Oracle9irelease1 (Automatic Segment Space Management), there is a concept of a low HWM. Why is there a HWM and a low HWM, this is due to the characteristics of automatic section space management. In the method section space management, when the data is inserted, if it is inserted into the new data block, the data block will be automatically formatted to wait for data access. In automatic section space management, when data is inserted into a new block, the data block is not formatted, but the block is formatted the first time it is accessed by the data block. So we need a watermark to indicate which blocks have been formatted. This watermark is called low HWM. Generally speaking, low HWM must be below or equal to HWM.

Oracle High watermark