Explain the differences between DELETE, TRUNCATE, and DROP in Oracle. oracletruncate

Explain the differences between DELETE, TRUNCATE, and DROP in Oracle. oracletruncate

Preface
In the past, when I was working on a project, I seldom paid attention to the details in the knowledge point. Today, I have the opportunity to differentiate several common functions in Oracle.

Syntax

• Delete from table_name
• Truncate table table_name
• Drop table table_name

Differences

• A condition can be written after a delete from statement, but not after a truncate statement. Generally, a condition is not written after a drop statement.
• The delete from record is deleted one by one, and each deleted line of record is logged, while the truncate deletes the entire page at a time. Therefore, only the page is released in the log. In short, delete from updates log, truncate basically does not update the log, and less transaction log space is used.
• After deleting an empty table from the delete from statement, an empty page is retained, and no page is left in the truncate statement.
• When the DELETE statement is executed using the row lock, the rows in the table are locked for deletion. Truncate always locks tables and pages, rather than locking rows.
• If there is an auto-incremental id column generated by identity, after delete from, it will still increase from the last number, that is, the seed will not change, and after truncate, the seed will restore the initial state.
• Truncate does not trigger the delete trigger, because the truncate operation does not record the deletion of each row.

Summary

• Truncate and delete only the data and do not delete the table structure (Definition). The drop statement will delete the table structure from being dependent on constrain, trigger, and index ); stored Procedures/functions dependent on the table will be retained but changed to invalid state.
• The delete statement is dml, which is placed in the rollback segement and takes effect only after the transaction is committed. If a trigger exists, the trigger is triggered during execution. Truncate, drop is ddl, the operation takes effect immediately, the original data is not placed in rollback segment, cannot be rolled back, the operation does not trigger.
• The delete statement does not affect the extent used by the table. The high watermark statement keeps the original position unchanged. Obviously, the drop statement releases all the space occupied by the table. The truncate statement is released to the minextents extent by default unless reuse storage is used. Truncate will reset the high water line (back to the beginning ).
• Speed. Generally, drop> truncate> delete.
• Security: Use drop and truncate with caution, especially when no backup is available. Otherwise, it will be too late to cry.
• To delete some data rows, use delete. Note that the where clause is included. The rollback segment must be large enough. To delete a table, use drop. If you want to retain the table and delete all the data, use truncate if it is not related to the transaction. If it is related to a transaction or you want to trigger a trigger, delete is used. If you want to organize fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data.

Detailed usage of delete, trancate, and drop in oracle

Delete: a dml statement that deletes data with specified conditions in the database. Syntax:
DELETE table WHERE a = B;
After executing this statement, you must use commit for submission to reflect the statement to the database.

TRUNCATE: (not trancate) is a DDL statement that quickly deletes all data in a specified table,
TRUNCATE is fast in both big and small tables. Similar to other DDL statements, it displays commit operations. Therefore, rollback cannot be performed after execution. TRUNCATE will reset the high horizontal line and all indexes. When you completely browse the entire table and index, the table after the TRUNCATE operation is much faster than the table after the Delete operation. TRUNCATE cannot trigger any Delete trigger. After the table is cleared, the index of the table and the table will be reset to the initial size, while the delete cannot.
Syntax: truncate table name

DROP: a ddl statement used to delete the entire table, delete a specified user, and delete a specified bucket.
Syntax:
Drop table table_name [cascade constraint]
Drop user user_name [cascade]
Drop tablespace tablespace_name including content and datafiles

The differences between oracle drop table and truncate table, and whether the related indexes are also deleted after the TABLE is deleted

The truncate table function is the same as the DELETE statement without the 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 a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.
Truncate table deletes all rows in the TABLE, but the TABLE structure, its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column.
To retain the ID Count value, use DELETE instead.
To delete TABLE definitions and data, use the drop table statement.
See