Differences between Truncate Delete Drop and HWM with High Water Level

Comparison between truncate and delete operations

Operation	Rollback	High waterline	Space	Efficiency
Truncate	No	Decrease	Reclaim	Fast
Delete	Yes	Unchanged	Do not recycle	Slow

Similarities:
Truncate, delete without where clause, and drop both delete table data.

 

Differences:
1. truncate and delete: delete only data. Do not delete the table structure (Definition)
The drop statement will delete the constraints, triggers, and indexes that the table structure is dependent on. Stored Procedures/functions that depend on the table will be retained, but it changes to the invalid status.
2. The delete statement is dml, which is put into the rollback segement and takes effect only after the transaction is committed. If a trigger exists, it is triggered during execution.

Truncate, drop is ddl, and the operation takes effect immediately. The original data is not stored in rollback segment, and cannot be rolled back. trigger is not triggered.

3. the delete statement does not affect the extent and high watermark used by the table) keep the original location unchanged. Obviously, the drop statement will release all the space occupied by the table. The truncate statement defaults to the minextents extent, unless you use reuse storage; truncate will reset the high water line (back to the beginning ).

4. Speed, in general: drop>; truncate>; delete

5. security: Use drop and truncate with caution, especially when no backup is available. otherwise, you will not be able to use it when you cry. If you want to delete some data rows, use delete. Note that the where clause is included. the rollback segment must be large enough. to delete a table, use drop
Delete all data to keep the table. if it is irrelevant to the transaction, use truncate. if it is related to the transaction or you want to trigger the trigger, delete is used. if you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

The DELETE statement deletes a row at a time and records one row in the transaction log.

Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

For tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

The truncate table cannot be used in the index view.

 

 

PS: two additional points: rollback and high water level

1. rollback

1. Data can be rolled back after being deleted in Oracle because it places the original data in the undo tablespace,

2. DML statements use undo tablespaces, DDL statements do not use undo, delete is a DML statement, truncate is a DDL statement, and other DDL statements are implicitly submitted. therefore, the truncate operation cannot be rolled back, but the delete operation can.

2. High Water Level

All Oracle tables have an upper limit for data storage (like the highest historical water level in a reservoir). We call this upper limit "high water mark" or "HWM. This HWM is a tag (a data block is used to record the high-water mark and so on) to indicate how many data blocks have been allocated to this table. HWM usually increases by five data blocks at a time.

The delete statement does not affect the data blocks occupied by the table. The high watermark statement keeps the original position unchanged.

The truncate statement releases space by default unless reuse storage is used; The truncate statement resets the high-water line.

Comparison of the two types of operations

SQL> analyze table t estimate statistics; Table analyzed. SQL> select segment_name, blocks from dba_segments where segment_name = upper ('T '); SEGMENT_NAME BLOCKS ---------------------------------------- T 24 SQL> select table_name, blocks, empty_blocks from user_tables where table_name = upper ('T '); TABLE_NAME BLOCKS EMPTY_BLOCKS ---------------------------------------------------- T 20 3

The USER_TABLES.BLOCKS column indicates that the table has been used.DatabaseThe number of blocks, that is, the waterline.

Note: USER_TABLES.BLOCKS EMPTY_BLOCKS (20 + 3 = 23) is one less than DBA_SEGMENTS.BLOCKSDatabaseBlock, because there isDatabaseThe block is reserved as the header. DBA_SEGMENTS.BLOCKS indicates allDatabaseThe number of blocks. USER_TABLES.BLOCKS indicates that you have usedDatabaseThe number of blocks (waterline ).

1 2 Next Page