Oracle queries a specific type of data in a table that is particularly slow and does not find a reason

Check a table today, the query table in a particular type of data is particularly slow, but query the whole table particularly fast, also did not find the reason, Baidu a bit, may feel (a small probability) is the following

Some people say:

Note that this table is changed too frequently. Copy the data that you don't want to delete to a temporary table, truncate the table, and then copy the data back. Deletes a temporary table.

High watermark HWM in Oracle table segments

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, which is not to say how much data is in the database, and Oracle scans such a large chunk of data, now imagine that if you have just created an empty table and you have a select operation, because the high watermark HWM in the lowest 0 position, no data block needs to be scanned and the scan time will 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 someone always says, how my table does not have a few data, but still so slow, this time in fact the mystery is here the high watermark.

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 space 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.

After the last deletion, re-import the data, OK.

Oracle high-water-line detailed

Oracle queries a specific type of data in a table that is particularly slow and does not find a reason