Comparison of deleted data DELETE statements and TRUNCATE statements for Oracle database

Comparison of deleted data DELETE statements and TRUNCATE statements for Oracle database

When the data in the table is not needed, you should delete the data and free up the occupied space, and delete the data in the table using the DELETE statement or the TRUNCATE statement, as described below.

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 the TRUNCATE statement is to delete all records 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.

The following examples illustrate:

1, first establish the user table:

CREATE TABLE Users (UserID   varchar2, username varchar2 (+), Userpass varchar2 (30));

Copy Code

2. Then insert a piece of data

Insert into users values (' 001 ', ' gavindream ', ' 123456 ');

3. Insert tens of thousands of data using the Copy Insert method

Insert into Users (userid,username,userpass) select * from users;

I've inserted 4,194,304 data, using delete deletion takes time: 90.964 seconds, and then inserting twice times more data, but using truncate takes only 2.215 seconds, as shown in:

Comparison of deleted data DELETE statements and TRUNCATE statements for Oracle database