Comparison between Delete statements and Truncate statements for oracle database data deletion

When the data in the table is not required, Delete the data and release the occupied space. To Delete the data in the table, use the Delete statement or the Truncate statement. Www.2cto.com 1. delete Statement (1) conditional deletion syntax format: delete [from] table_name [where condition]; for example, delete data whose userid is '001' in users table: delete from users where userid = '001'; (2) delete the entire table unconditionally. Syntax: delete table_name; for example, delete all data in the user table: delete users;
2. The Truncate statement www.2cto.com uses the Truncate statement to delete all records in the table. Syntax format: Truncate [table] table_name; (1) Delete all records without retaining the space occupied by the record Truncate [table] table_name [drop storage]; for example: deleting all data in the users table does not occupy space: Truncate table users drop storage; because the drop storage keyword is used by default, the drop storage can be omitted;
(2) Delete the space occupied by reserved record Truncate [table] table_name [reuse storage]; for example, delete all data in the users table and save the space occupied: Truncate table users reuse storage; iii. Comparison of the two delete statements because records are deleted one by one when the delete statement deletes records, but no rollback information is generated when the Truncate statement deletes data; therefore, if you need to delete a large amount of data, using delete will occupy a large amount of system resources, while using Truncate will be much faster. The following example shows how to create a user table: create table users
(
Userid varchar2 (20 ),
Username varchar2 (30 ),
Userpass varchar2 (30)
); 2. insert a data insert into users values ('001', 'gavindream', '123 '); 3. insert tens of thousands of data records using the copy insert method insert into users (userid, username, userpass) select * from users; I have inserted 4194304 data records, and it takes the following time to delete data using delete: 90.964 seconds, and then two times the data is inserted, but the time spent using truncate is only 2.215 seconds, as shown in: