Oracle delete high water level line handling problems

Recently, a large volume of data in Oracle tables needs to be deleted due to slow queries and updates. It is found that the query speed after deletion is still very slow. It turns out that the high watermark of delete oracle has not declined.

In oracle, after delete is used to delete data, the storage capacity of the database is not reduced, and after a table is deleted using delete, the query speed of this table is the same as before deletion, it will not change.

Because oralce has an HWM high water level, it is the highest water level in the space used by an oracle table. After the data is inserted, the high waterline will rise. However, if you delete the data using the delete statement, the data will be deleted, but the high waterline will not decrease, it's still the high level you used to delete data. Unless you use truncate to delete data. Therefore, this high water level line will only increase in daily addition and deletion operations and will not fall, so the database capacity will only increase and will not decrease. When you use the select statement to query data, the database scans data blocks below the high waterline. Because the high waterline does not change, the scanning time will not be reduced, therefore, after using delete to delete data, the query speed is the same as before delete.

Solution:

1. First export the table, truncate the table, and finally import the table.

2. Move the table in the bucket, but the rowid will be disrupted, so you need to re-create the index.

3. If it is oracle 10 Gb, but directly update the table's high water level line.

Corresponding SQL:

9i:

Create table aa_bak as select * from aa where record_time> sysdate-10;

Truncate table aa;

Insert into aa select * from aa_bak;

Drop table aa_bak;

10g version

Alter tablename enable row movement;

Alter tablename shrink space;

Practice:

Select count (*) from wlkp_fp_kj

Query results show 2301245 records

We need to delete some data.

Before deleting a table, check the table's high watermark line (wlkp_fp_kj is a partitioned table)

SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name = 'wlkp _ FP_KJ'

1 2 Next Page