Delete
1.DML data can be recovered by rollback before commit
2.Remove any subset of data
3.Delete is slower when table have numerous indexes and triggers
4.DML triggers fire for delete mthod
5.data can be recovered after commit by flashback method
6.high water mark will remain the same
-------注意7,8
7.data deleted info capture with returning clause
8. you can not delete data if function based index is invalid
9. can not delete data from complex views.
10. Space is not freed
Truncate
1.Truncated Data can not be recoverd but truncated data is also logged
2.truncate all or partition or subpartition data.
--注意3,5,6,7
3. truncate is faster as no logs we have to maintain ,no trigger firing
4. reset the high water mark , space is released except reuse method
5. can not truncate table indivisually if it is part of cluster
6. can not truncate table with referential integerity constraint enable
7. if table is not empty unuseable indexes become useable
More details at my blog :
http://arunkaushikoracle.blogspot.in/2012/10/diff-bw-delete-truncate-in-oracle-db-on.html
8. if domain index on table is invalid or in_progess state then we can not truncate the table
8.we can not truncate a simple view , we can truncate MV but not simple view.
For More Details on all this point with examples see my blog.
