Differences between DELETE and TRUNCATE statements in Oracle and their advantages and disadvantages

In general, we can use the TRUNCATE or DELETE statement to DELETE records in the table. The TRUNCATE statement is usually more efficient, but you need to pay attention to it. TRUNCATE is a DDL statement, which means that Oracle will automatically submit the statement and the transactions in the current session after the statement is run, so we cannot roll back the TRUNCATE statement.

In general, we can use the TRUNCATE or DELETE statement to DELETE records in the table. The TRUNCATE statement is usually more efficient, but you need to pay attention to it. TRUNCATE is a DDL statement, which means that Oracle will automatically submit the statement and the transactions in the current session after the statement is run, so we cannot roll back the TRUNCATE statement.

In general, we can use the TRUNCATE or DELETE statement to DELETE records in the table. The TRUNCATE statement is generally more efficient, but its usage requires special attention. TRUNCATE is a DDL statement, which means that Oracle will automatically submit the statement and the transactions in the current session after the statement is run, so we cannot roll back the TRUNCATE statement. At the same time, because TRUNCATE is a DDL statement, we cannot truncate two different tables in a transaction. As follows:

The TRUNCATE statement deletes all data in the COMPUTER_SYSTEMS table:

SQL> truncatetable computer_systems;

When you truncate a table, by default, all space previously allocated to the table will be reclaimed except for the space defined by the MINEXTENTS table-level storage parameters of the table. If you want the TRUNCATE statement not to recycle the currently allocated extent, you can use the REUSESTORAGE clause in the truncate statement:

SQL> truncatetable computer_systems reuse storage;

You can query the DBA/ALL/USER_EXTENTS view to check whether the extent of the table has been recycled. For example:

Set pagesize 50000

Sets long 9999

Selectdbms_metadata.get_ddl ('table', 'test', 'Scott ') from dual;

DBMS_METADATA.GET_DDL ('table', 'test', 'Scott ')

--------------------------------------------------------------------------------

Create table "SCOTT". "TEST"

("EMPNO" NUMBER (4, 0 ),

"ENAME" VARCHAR2 (10 ),

"JOB" VARCHAR2 (9 ),

"MGR" NUMBER (4, 0 ),

"HIREDATE" DATE,

"SAL" NUMBER (7,2 ),

"COMM" NUMBER (7,2 ),

"DEPTNO" NUMBER (2, 0)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1

MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 freelist groups 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLAS

H_CACHE DEFAULT)

TABLESPACE "USERS"

SQL> insert into test select * from emp;

14 rows created.

SQL>/

14 rows created.

SQL>/

14 rows created.

SQL>/

14 rows created.

SQL>/

14 rows created.

SQL> selectcount (*) from user_extents where segment_name = 'test ';

COUNT (*)

----------

2

SQL> truncatetable test;

Table truncated.

SQL> selectcount (*) from user_extents where segment_name = 'test ';

COUNT (*)

----------

1

If you need to select rollback without submitting the operation when deleting data, you should select the DELETE statement. However, the disadvantage of the DELETE statement is that it generates a large amount of undo and redo information. Therefore, for large tables, the TRUNCATE statement is usually the fastest way to delete table data. Another advantage of the TRUNCATE statement is that it can reset the table's high water level line to 0. We can use this feature of the Truncate statement to adjust the query performance of the entire table. The TRUNCATE statement is used to release the high waterline of the table, so that the Row Records in the table are stored in the blocks below the high waterline, this greatly improves the efficiency of full table scanning. In addition to being unable to roll back, the Truncate statement has the following Disadvantages: If a primary key table defined in a parent table is referenced as a foreign key constraint, even if the child table contains 0 rows of records, the parent table cannot be deleted. In this scenario, Oracle throws the following exception when trying to truncate the parent table:

SQL> truncatetable dept;

Truncate table dept

*

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Oracle's internal mechanism will block the truncate parent table, because in a multi-user system, during the interval between you first truncate the child table and then truncate the parent table, another session may insert records into the subtable. In this scenario, you must temporarily disable the foreign key constraint of the table before executing the TRUNCATE statement, and then re-enable the foreign key constraint.

Compare the actions of the TRUNCATE and DELETE statements. Oracle allows the use of the DELETE statement to DELETE records in the parent table when the child table references a foreign key constraint. This is because the DELETE operation will generate the undo which belongs to the read-consistent operation and can be rolled back. When using the DELETE statement, you must use COMMIT or ROLLBACK to complete the transaction.

SQL> delete fromtest;

28 rows deleted.

SQL> commit;

Commit complete.

Note: The commit statement can make the delete statement operation take effect permanently. You can also use other methods to implicitly commit transactions, such as executing subsequent DDL statements in the delete statement or exiting the Client Connection Tool (sqlplus) in a normal way ).

If the ROLLBACK statement is executed without the COMMIT statement, the data in the table is the same as before the DELETE statement is executed. When executing a DML statement, you can query the V $ TRANSACTION View to learn the TRANSACTION details. For example, if data is inserted into the table, before executing COMMIT or ROLLBACK, you can view the information of the active transactions currently connected to the session as follows:

SQL> insert into test select * from emp;

14 rows created.

SQL> selectTADDR, USERNAME from v $ session where username = 'Scott ';

TADDR USERNAME

--------------------------------------

36C71818 SCOTT

SQL> selectXIDUSN, XIDSLOT, XIDSQN from v $ transaction where addr = '36c71818 ';

XIDUSN XIDSLOT XIDSQN

------------------------------

6 3 746

SQL> commit;

SQL> selectTADDR, USERNAME from v $ session where username = 'Scott ';

TADDR USERNAME

--------------------------------------

SCOTT

The difference between a DELETE statement and a TRUNCATE statement:

The DELETE statement can be submitted or rolled back. The TRUNCATE statement is automatically submitted after execution;

The DELETE statement generates UNDO information. The TRUNCATE statement is not generated or generates a small amount of data;

The DELETE statement cannot release the high level of the table. TRUNCATE can;

The DELETE statement is not affected by the foreign key constraint, and the TRUNCATE statement is affected;

When deleting large table data, the DELETE efficiency is much lower than that of TRUNCATE.

There is also a very fast way to delete table data: drop the table first and then recreate the table. However, after recreating the table, you need to recreate all the indexes, constraints, authorizations, and triggers that belong to the table. In addition, when you drop a table, it will be unavailable for a short time during the table reconstruction process. Generally, this method can only be used in development or test environments.

Author: xiangsir

9063597

QQ: 444367417

MSN: xiangsir@hotmail.com