Sql-truncate the difference between deleting a table and delete

The QL truncate TABLE command deletes the complete data from an existing table.

You can also use the drop table command to delete the complete table, but it removes the complete table structure from the database and, if you want, you can store some data again and write it back to the table.

Grammar

The basic syntax for TRUNCATE TABLE is as follows:

TRUNCATE TABLE  table_name;

1.DELETE
? DML language
? can be rolled back
? can be conditionally deleted

DELETE from table name
WHERE condition

2.TRUNCATE TABLE
? DDL language
? cannot be rolled back
? All table contents are deleted by default
Deletes faster than delete.

TRUNCATE Table Name

1, Truncate on various tables whether large or small are very fast. If there is a rollback command delete will be revoked, and truncate will not be revoked. 
2. Truncate is a DDL language, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate. 
3, TrunCate will reset the high-level line and all indexes. When a full table and index are fully browsed, the table after the truncate operation is much faster than the table after the delete operation. 
4. Truncate cannot trigger any delete triggers. 
5. When the table is emptied, the index of the table and table is reset to the initial size, and delete cannot. 
6. Cannot empty the parent table. 

in Oracle, when you delete data using Delete, the storage capacity of the database is not reduced, and when you use Delete to delete data from a table, the speed of the query is the same as before the deletion, and will not change. 

because the Oralce has a HWM high water level, it is one of the Oracle tables that uses the highest watermark of space. When the data is inserted, the high watermark will rise, but if you useDelete statementDelete the data, although the data is deleted, but the high watermark is not reduced, or you just deleted the data before the high water level. Unless you use TRUNCATE to delete data. Then, the high watermark in daily additions and deletions will only rise, will not fall, so the database capacity will only rise, will not fall. and usingSELECT statementwhen querying the data, the database scans the blocks below the high watermark, because the high watermark does not change, so the scan time is not reduced, so the query will be the same speed as the delete before the delete data is used. 

Delect does not require a column name or wildcard character. Delect Delete the entire row instead of deleting the column. To delete the specified column, use the UPDATE statement.

Sql-truncate the difference between deleting a table and delete