Truncate table, delete, and drop table differences

Truncate Table , the difference between delete and drop table

TRUNCATE table is functionally the same as a DELETE statement without a WHERE clause: Both delete all rows in the table. However, TRUNCATE TABLE is faster than DELETE and uses less system and transaction log resources.

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 table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. The count value used for the new row identity is reset to the seed of the column. If you want to preserve the identity count value, use DELETE instead.

If you want to delete the table definition and its data, use the DROP table statement.

For tables referenced by the FOREIGN KEY constraint, you cannot use TRUNCATE table and you should use a DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not recorded in the log, it cannot activate the trigger.

TRUNCATE table cannot be used for tables that participate in an indexed view.

Above is the SQL online book, it seems that few books mention truncate TABLE, also seems to use it rarely. Seeing a problem today is exactly what it takes.

Problem: There is a table with 100 million data, only need to keep 5 of them, the other delete, how to do?

This needs to be done with TRUNCATE TABLE, as follows:

Select 5 data into #临时表 table from 100 million data

Table of TRUNCATE TABLE 100 million data

Insert 100 million data in Ox x table select * FROM #临时表

drop table #临时表

Because truncate is a DDL command, the execution of the data is not placed in the rollback segment, does not produce rollback data, does not produce redo Log. Truncate, Drop and other DDL commands are implicitly committed.

Using truncate will:

1. Clear the corresponding index (not drop, still retain extent);

2. Constrained by foreign key reference (FOREIGN key). Of course, you must first disable or drop the corresponding foreign key reference, in order to truncate, note that there is no cascade option.

3. Do not trigger the DELETE trigger

4. If drop STORAGE (default) is used, except for the specified minextents, the other extent is reassigned next_extent minextents after setting extent,high-water Mark resets to the first block in the table. Use reuse storage to reserve the use space of the table, preserving the original extension, but not merging; The position of the HWM (high water level) is reset to the first block. When the drop is storage, the table and table indexes are shortened, the table is shrunk to a minimum, and the next parameter is reset. Reuse storage does not shorten the table or adjust the next parameter.

The Truncate statement default (by default, drop storage) space is released to minextents extent unless reuse storage is used (if reuse storage is used, the space left after these deletions is persisted); Truncate will reset the high watermark (back to the beginning). If you are defragmenting the inside of the table, you can use truncate to keep up with reuse stroage and re-import/insert the data.

The Delete statement does not affect the extent occupied by the table, and the high waterline (HI Watermark) retains its original position. The DELETE statement is DML, which is placed in the rollback segement, which takes effect after the transaction is committed, and is triggered when the corresponding trigger is executed.

The drop statement frees all the space occupied by the table. The drop statement will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index (indexed); Stored procedures/functions that depend on the table are preserved, but become invalid states.

Truncate:

The drop storage is the default value, which differs from the reuse storage whether the free space for rows in the purge table is reclaimed to tablespace, and if drop storage is used, Recycled frespace can be used by other segment, otherwise this part of the space can only be used for their own use.

Another difference is that with reuse storage you can reduce the lock time for tables and data dictionaries, especially for large tables, and the rest and deallocate UNUSED to gradually reclaim space.

For HWM, either drop storage or reuse storage will shrink back to the first block.

Speed, in general: drop> truncate > Delete

Truncate table, delete, and drop table differences