In the oracle query table, a specific type of data is very slow, and no reason is found. oracle does not

In the oracle query table, a specific type of data is very slow, and no reason is found. oracle does not

When I look up a table today, it is very slow to query a specific type of data in the table, but the whole table is very fast, and no reason is found. Baidu may feel (very low) is the following

Some people say:

This indicates that the addition, deletion, and modification of the table are too frequent. copy the data that does not need to be deleted to a temporary table, truncate the table, and copy the data back. Delete the temporary table.

HWM in the Oracle table segment

In the storage of Oracle data, you can think of the storage space as a reservoir, and the data as water in the reservoir. There is a line of water in the reservoir called a waterline. in Oracle, this line is called a High-warter mark (HWM ). When a database table is created, there is no data, so the watermark line is empty at this time, that is, HWM is the lowest value. When the data is inserted, the High-level line will rise, but here there is also a feature, that is, if you use the delete statement to delete the data, although the data is deleted, however, the high water level has not been lowered, but it is still as high as before you deleted the data. That is to say, this high water level line will only rise in daily addition, deletion operations, and will not fall.

The following describes the features of Select statements in Oracle. The Select statement scans the data in the table once, but how many data storage blocks are scanned does not mean that Oracle scans such a large data block as to how much data is in the database, instead, Oracle scans data blocks below the high waterline. Now imagine that if you have just created an empty table and performed a Select operation, the HWM of the high watermark line is at the lowest position, therefore, no data blocks need to be scanned, and the scanning time is very short. If at this time, you first Insert 10 million data records, and then use the delete statement to delete the 10 million data records. Because 10 million pieces of data are inserted, the high water level line is here 10 million pieces of data. Later, when the 10 million data records were deleted, because the delete statement does not affect the high waterline, the high waterline is still in the 10 million data records. At this time, the select statement is used again for scanning. Although there is no data in the table at this time, the scanning is based on the high water level, therefore, you need to scan the storage space of 10 million pieces of data once. That is to say, the time required for this scan is the same as that required for scanning 10 million pieces of data. So sometimes some people often say that my table does not have a few data records, but it is still so slow. In fact, the mystery is the high water level here.

Is there a way to reduce the high water level line? In fact, there is a simple method, that is, using the TRUNCATE statement to delete data. When the TRUNCATE statement is used to delete the data of a table, it is similar to re-establishing the table, not only deleting the data, but also clearing the HWM and restoring it to 0. Therefore, if you need to clear the table, you can use the TRUNCATE statement to delete the table when using the TRUNCATE statement to delete the data, especially the temporary storage table with a large amount of data.

In Manual Segment Space Management, there is only one HWM in the Segment, but in the Automatic Segment Space Management added in Oracle9iRelease1, there is a low HWM concept. Why is there another low HWM with HWM? This is caused by the feature of automatic segment space management. When data is inserted into a new data block, the data block is automatically formatted and waiting for data access. In automatic segment space management, after data is inserted into a new data block, the data block is not formatted. Instead, the block is formatted when the data block is accessed for the first time. So we need another waterline to mark the formatted block. This waterline is called low HWM. In general, low HWM must be lower than or equal to HWM.

After the deletion, re-import the data.

Detailed description of oracle high level line