DELETE Statement:
- DELETE is a DML command.
- Can rollback in DELETE.
- Triggers get fired.
- Can use conditions (WHERE clause) in DELETE.
- DELETE does not reset the High Water Mark
for the table
TRUNCATE statement:
- TRUNCATE is a DDL command.
- TRUNCATE is much faster than DELETE.
Reason: When you type DELETE all the data get
copied into the Rollback Tablespace first. Then delete operation gets performed. That way when you type ROLLBACK after deleting a table, you can get back the
data (The system get it for you from the Rollback Tablespace). All this process
takes time. But when you type TRUNCATE, it removes data directly without copying
it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you truncate
you can’t get back the data.
- Cannot roll back in TRUNCATE. TRUNCATE removes
the record permanently.
- The trigger doesn't get fired
- Cannot use conditions (WHERE clause) in
TRUNCATE.
- TRUNCATE command resets the High Water Mark
for the table
- This command is used to delete all the rows
from the table and free the space containing the table.
- When a table is truncated all the references
to the table will be valid.
DROP Statement:
- DROP is a DDL command.
- No DML triggers will be fired.
The DROP command is used to remove an object
from the data dictionary. If you drop a table, all the rows in the table are
deleted and the table structure is removed from the database. Once a table is
dropped we cannot get it back, so be careful while using the DROP command. When a
table is dropped all the references to the table will not be valid.
If a table is dropped, all the relationships with
other tables will no longer be valid, the integrity constraints will be
dropped, and grant or access privileges on the table will also be dropped, if want
uses the table again it has to be recreated with the integrity constraints,
access privileges and the relationships with other tables should be established
again.
We cannot recover the table before Oracle 10g.
But Oracle 10g provides the command to recover it by using the command
(FLASHBACK)
