Two ways to delete data from an Oracle database

When the data in the table does not need to be, you should delete the data and free up the space occupied; Delete the data in the table has delete and truncate two kinds of ways, the following are described separately;

One, DELETE statement

(1) Conditional deletion

syntax format:delete [from] table_name [where condition];

For example: Delete data from the users table with the UserID ' 001 ': Delete from users where userid= ' 001 ';

(2) Unconditionally delete entire table data

syntax format:DELETE table_name;

For example: Delete all data from the user table: delete users;

Second, truncate statement

Using truncate is to delete all the data in the table;

syntax Format: Truncate [TABLE] table_name;

(1) Delete all records do not keep record space

Truncate [TABLE] table_name [drop storage];

For example, deleting all data from the users table does not save space: Truncate table users drop storage; Drop storage can be omitted because the drop storage keyword is used by default;

(2) Delete all records to keep the record occupied space

Truncate [TABLE] table_name [reuse storage];

For example: Delete all data from the users table and save space: Truncate table users reuse storage;

Comparison of two DELETE statements

Since the DELETE statement deletes the record, the record is deleted one at a time, and the TRUNCATE statement does not produce fallback information when it deletes the data, so if you need to delete large amounts of data, using delete consumes more system resources, and if you use truncate, it is much faster.

TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table.

             but TRUNCATE TABLE is faster than DELETE, and less system and transaction log resources are used.

The DELETE statement deletes one row at a time and records an entry in the transaction log for each row that is deleted.
TRUNCATE table deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log.

 
Truncate,delete,drop to compare:
TRUNCATE TABLE: delete content, free space, but do not delete definition.
Delete TABLE: Deletes the definition without deleting the content and does not free up space.
drop TABLE: Removes content and definitions and frees up space.

Two ways to delete data from an Oracle database