Impact of delete and truncate Clearing table data on HWM

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 mark (or high water level line), it is the highest water level line used by a table in oracle. 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 before delete.

Therefore, we can conclude that the delete statement does not release the tablespace. To release the tablespace, we need to use the truncate statement.
Let's test it.
1) create a test table and insert test data;
SQL> CREATE TABLE w. w AS SELECT * FROM dba_objects;

Table created.
2) Check the allocated block and partition size in the table;
SQL> SELECT segment_name, segment_type, blocks, extents FROM dba_segments WHERE segment_name = 'W'; ------ the value of segment_name must be larger

SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
-----------------------------------------------------------------------------------------------------------------------
W table 4264 49
The blocks column lists the number of allocated data blocks and the extents column lists the number of allocated blocks.
3) analysis table w;
SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.
4) query the high watermark line of the table;
SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
4214 50 294935
The blocks column is a high water level line (which occupies the number of data blocks in the w table), and The empty_blocks column is the number of free blocks in the w table.
5) Now we will delete the w table, in view the high water level line [help house http://www.bkjia.com]
SQL> DELETE FROM w;

290232 rows deleted.

SQL> commit;

Commit complete.

SQL> ANALYZE TABLE w ESTIMATE STATISTICS;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM USER_TABLES WHERE TABLE_NAME = 'W ';

BLOCKS EMPTY_BLOCKS NUM_ROWS
--------------------------------
4214 50 0 ------ the high water level line does not drop, or 4214

1 2 Next Page