oracle:sql:delete_drop_or_truncate
Oracle - SQL - Delete, Drop, or Truncate
| DELETE | The DELETE command is used to remove rows from a table. |
| A WHERE clause can be used to only remove some rows. | |
| If no WHERE condition is specified, all rows will be removed. | |
| After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. | |
| Note that this operation will cause all DELETE triggers on the table to fire. | |
| DROP | The DROP command removes a table from the database. |
| All the tables' rows, indexes and privileges will also be removed. | |
| No DML triggers will be fired. | |
| The operation cannot be rolled back. | |
| TRUNCATE | TRUNCATE removes all rows from a table. |
| The operation cannot be rolled back and no triggers will be fired. | |
| TRUCATE is faster and does not use as much undo space as a DELETE. |
TRUNCATE
BEGIN TRAN TRUNCATE TABLE tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest
NOTE:
- Truncate resets identity of table.
- It locks the entire table.
- It is DDL (Data Definition Language).
- The WHERE clause cannot be used with it.
- Trigger is not fired while truncate.
DELETE
BEGIN TRAN DELETE FROM tranTest SELECT * FROM tranTest ROLLBACK SELECT * FROM tranTest
NOTE:
- Delete does not reset identity of table.
- It locks the table row.
- It is DML (Data Manipulation Language),
- The WHERE clause can be used with it to filter data to delete.
- Trigger is fired.
DELETE with WHERE
DELETE FROM TABLE_NAME WHERE example_column_id IN (1,2,3)
oracle/sql/delete_drop_or_truncate.txt · Last modified: 2021/08/08 17:53 by peter