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 data is deleted using delete, the query speed is the same as that before delete: www.2cto.com
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-index the table. 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; version 10: www.2cto.com alter tablename enable row movement; alter tablename shrink space;
Practice: select count (*) from wlkp_fp_kj: the query result shows 2301245 records. before deleting a part of data, we first check the table's high water level line (wlkp_fp_kj is a partitioned table) SELECT segment_name, segment_type, blocks FROM dba_segments WHERE segment_name = 'wlkp _ FP_KJ'

Delete data SQLSql code select count (*) from wlkp_fp_kj where kprq <to_date ('2017-12-01 14:00:00 ', 'yyyy-mm-dd, hh24: mi: ss ') and kprq> to_date ('1970-11-01 14:00:00 ', 'yyyy-mm-dd, hh24: mi: ss') 2011 records
Delete statement SQL code delete from wlkp_fp_kj where kprq <to_date ('2017-12-01 14:00:00 ', 'yyyy-mm-dd, hh24: mi: ss ') and kprq> to_date ('2017-11-01 14:00:00 ', 'yyyy-mm-dd, hh24: mi: ss') after deletion, query the high watermark of the WLKP_FP_KJ table.

It is found that the query results remain unchanged as before they were deleted. It means that the DELETE statement will not drop down when the water level is high. In method 2, we can execute the following statement: SQL code alter table wlkp_fp_kj enable row movement; alter table wlkp_fp_kj shrink space; after the execution, the high watermark of WLKP_FP_KJ is queried.

It is obvious that the high water level drops after execution.