Difference between delete and truncate in oracle database ?- SQL Interview question
In my previous blog , I have written about the future of oracle dbs jobs. If you are going for sql or oracle interview then most of the interviewer expect that you should know the difference between delete and truncate in oracle database.
let’s see the difference between delete and truncate statement in oracle database.
Delete in SQL:
delete from table_name where column_name > value;
delete from employee where salary > 10000;
here in above example employees whose salary greater than 10000 will be deleted.
- Delete is the DML operation.
- Delete can be used with where condition to delete certain rows based upon the condition
- Statement of delete takes more time as compare to truncate
- Operation can be rollback after delete statement.
Truncate in SQL
truncate table table_name;
truncate table employee;
In above example all the rows will be deleted from employee table.
- Truncate is useful to delete all rows from table.
- Truncate do not delete the table structure. Drop delete the table content and table structure. Delete statement deletes the rows from table with where condition.
- Once Truncate the rows then it can not be rollback.
Above difference is generic for all the rdbms databases including mysql,oracle etc.