What is the difference between TRUNCATE and DELETE?
Answer: One difference is when TRUNCATE a table you can not rollback the data. But when you DELETE data from a table you can execute rollback to get back your deleted data (that is if you run this before you commit the transaction).
Anther difference is that the TRUNCATE command reset the high water mark on the table. So table access is much faster. DELETE does not reset the high water mark.
For example, suppose you have a table with 10 million records. You want to delete all these data and then repopulate the table with another 10 million records. And you want to do this regularly (daily/monthly etc). Then it is advisable to use TRUNCATE command to free up the high water mark.
Otherwise if you use the delete command, the high water mark will just grow and grow and make the table very slow.
How to Enable or Disable a constraint an Oracle constraint?
Answer: Sometime you may want to ENABLE or DISABLE a constraint for some reason. You can do so by using the syntax below:
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
For example if you have a table called EMP which has a constraint called “emp_sal” then you can enable or disable the constraint using the commands:
ALTER TABLE emp DISABLE CONSTRAINT emp_sal;
ALTER TABLE emp ENABLE CONSTRAINT emp_sal;
Recent Comments