In today’s article, we will learn how to delete data from a table and the differences between DELETE and TRUNCATE commands.
Data from a table can be deleted with DELETE and TRUNCATE commands. But there are differences between them.
These differences are as follows.
By default, Oracle deals (deallocates) the space held by the table with the TRUNCATE operation.
If we do not want the space held by the table to be released, we must perform the TRUNCATE operation with the word “REUSE STORAGE”.
In this way, Oracle is not busy with the field allocation process while new records are inserted into the table.
|
1 2 3 |
SQL> TRUNCATE TABLE ACCOUNT REUSE STORAGE; Table truncated. |
High-Water Mark: The border between used and unused areas within a segment is called the high-water mark of the table.
Before data is inserted into a new table, no blocks are used and the high-water mark is zero.
When large volumes of data are deleted with DELETE, Oracle will scan every block even if there is no data in the table, since the high-water mark will not change.
This is a serious performance problem that appears as a “full-table scan”.
