Comparison of Delete and TRUNCATE statements in Oracle

A, DELETE statement

(1) Conditional deletion

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

For example: Delete data from the users Table of UserID ' 001 ': Delete from users where userid= ' 001 ';

(2) Unconditionally delete the entire table data

Syntax format: delete table_name;

For example, delete all data in the user table: delete users;

Second, the TRUNCATE statement

Use the TRUNCATE statement to delete all records in a table.

Syntax format: Truncate [table] table_name;

(1) Delete all records do not keep the record occupied space

Truncate [TABLE] table_name [drop storage];
For example, deleting all data in 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 records occupied space

Truncate [TABLE] table_name [reuse storage];

For example, delete all data in the users table and save space: Truncate table users reuse storage;

Comparison of two kinds of deletion statements

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

The following examples illustrate:

1, first set up user table:

The code is as follows	Copy Code
CREATE TABLE Users ( UserID VARCHAR2 (20), Username VARCHAR2 (30), Userpass VARCHAR2 (30) );

2, then insert a piece of data

The code is as follows	Copy Code
Insert into users values (' 001 ', ' gavindream ', ' 123456 ');

3. Inserting tens of thousands of data using the Copy insertion method

The code is as follows	Copy Code
Insert into the Users (userid,username,userpass) select * from users;

I inserted 4,194,304 of the data and used delete to delete the time: 90.964 seconds, then inserted twice times the data, but use truncate time only for 2.215 seconds, as shown in the following figure:

If we don't get it, we're going to look at a test instance.

The way to delete data in a table is Delete,truncate,
They all delete the data in the table, not the table structure, delete the data from the entire table or delete the data from one or N of the table that satisfies the condition, and truncate can only delete the data from the entire table, we usually take the delete operation as the deletion table, And the truncate operation is called truncating the table.

truncate operation and Delete Operation comparison

Operation	Rolling back	High watermark	Space	Efficiency
Truncate	No	Fall	Recovery	Fast
Delete	OK	Not change	Do not recycle	Slow

Let's look at their differences by using an instance
1. Roll Back
The first thing to understand is two.
1. Data deletion in Oracle can be rolled back because it puts the original data in the Undo table space,
The 2.DML statement uses the undo tablespace, the DDL statement does not use Undo, and the delete is a DML statement, TRUNCATE is a DDL statement, and the Outer DDL statement is implicitly committed.
So truncate can not be rolled back, and the delete operation.
Compare two operations (create a new table first and insert the data)

The code is as follows	Copy Code
Sql> CREATE TABLE T 2 ( 3 I number 4); Table created. Sql> INSERT INTO T values (10); Sql> commit; Commit complete. Sql> select * from T; I ---------- 10

Delete Deletes, and then rolls back

The code is as follows	Copy Code
Sql> Delete from T; 1 row deleted. Sql> select * from T; No rows selected #删除后回滚SQL > Rollback; Rollback complete. Sql> select * from T; I ---------- 10

Truncate truncate the table and then rolls back.

The code is as follows	Copy Code
sql> truncate TABLE t; Table truncated. sql> rollback; Rollback complete. Sql> select * from T; No rows selected

The Visible delete Delete table can also be rolled back, and truncate truncate the table cannot be rolled back. (If the delete operation is not committed)
2. High Waterline
All Oracle tables have a ceiling for data (much like the highest water level in a reservoir), and we call this cap "high water mark" or HWM. The HWM is a tag (a block of data that is used to record high water marks, etc.) to indicate how many blocks of data have been allocated to the table. The HWM usually increases by 5 blocks at a time.
The DELETE statement does not affect the block of data that the table occupies, and the high watermark (watermark) keeps the original position fixed
Truncate statement by default, space is released unless the reuse storage is used; Truncate will reset the high waterline
Compare the two operations below

The code is as follows	Copy Code
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

User_tables. The BLOCKS column represents the number of database blocks that have been used in the table, that is, the watermark. Note: User_tables. BLOCKS empty_blocks (20+3=23) than dba_segments. Blocks a database block, because there is a database block reserved for use as a table header. Dba_segments. BLOCKS represents the number of all the database blocks assigned to this table. User_tables. Blocks represents the number of database blocks (watermarks) that have been used.
Delete deletes the table,

code is as follows

copy code

sql> Delete From T; 10000 rows deletedsql> commit; Commit complete. sql> analyze table T estimate statistics; Table analyzed. Sql> Select Table_name,blocks,empty_blocks from User_tables where Table_name=upper (' t '); table_name                          BLOCKS empty_blocks ---------------------------------------- ---------------------------------------------------------------- t                                        20             3

Truncate TRUNCATE TABLE

The code is as follows

Copy Code

sql> TRUNCATE TABLE t; Table truncated. sql> analyze table T estimate statistics; Table analyzed. Sql> Select Table_name,blocks,empty_blocks from User_tables where Table_name=upper (' t '); table_name                          BLOCKS empty_blocks ---------------------------------------- -------------------------------------------------------- t                                         0             7

Visible, delete table, block (high watermark) unchanged, and TRUNCATE TABLE blocks (high watermark) to 0
Now we also see blocks+empty_blocks=7, which is the default once 7+1 (header) in Oracle allocation area = 8 Blocks;
The effect of high watermark: HWM The operation of the database has the following effects:
A full table scan is usually read out until all of the HWM tags belong to the table database block, even if there is no data in the table.
B) If you use the APPEND keyword when inserting data, even if there are free database blocks below HWM, the data block above HWM is used at the time of insertion, and HWM automatically grows.
Therefore, a high watermark is an important parameter for Oracle Optimization
3. Space
Since the high watermark is used to indicate how many blocks of data have been allocated to the table, the high watermark can also be understood as the space occupancy of the table.
Even if delete deletes all the data in the table, HWM or for the original value, so there is so much space allocated to the table, that is, its space has not been reclaimed,
and TRUNCATE table after the high watermark to 0, then it means that there is no space allocated, that is, its space is recycled.
4. Efficiency
To see delete,truncate is more efficient, build a large table, and then see how long it takes them to delete the tables separately. The
has a fairly figurative metaphor: the leader give you two books to let you throw away, delete is you keep in front of the copier, the page of a page is torn off to copy, and then a page to throw into the trash, truncate is directly to the two books thrown into the trash, that fast that slow self-evident.
Insert 100,000 records in the table and open the time

The code is as follows	Copy Code
Sql> set timing on; Sql> begin 2 for I in 1..100000 loop 3 INSERT into T values (' 10 '); 4 commit; 5 end Loop; 6 end; 7/ Pl/sql procedure successfully completed. elapsed:00:01:12.50

Delete Table

The code is as follows	Copy Code
Sql> Delete from T; 100000 rows deleted. elapsed:00:00:20.09

Truncate TRUNCATE TABLE

The code is as follows	Copy Code
#先把表回滚SQL > Rollback; Rollback complete. elapsed:00:00:17.36 Sql> Select COUNT (*) from T; COUNT (*) ------------------- 100000 elapsed:00:00:00.01 sql> truncate TABLE t; Table truncated. elapsed:00:00:00.20

Visible deletes a table of the same size, delete takes 20.09 seconds, and truncate only takes 0.2 seconds