Difference between Delete and Truncate command in SQL

Today, I am going to discuss about the facts behind the delete query and difference between delete query and truncate query.

Now you will come to know about why truncate query is faster than the delete query and what happens when you fire a delete query on a table.

DELETE

Let me first brief you about delete query, Delete command is used to delete rows from the table you can also filter rows to delete from the table using where clause.

Syntax is shared below:

Delete from users where name = ‘vishal’

Where users is table name and  name is column of the table and using where clause I am filtering rows from users to delete from the table with name vishal.

And if query is fired without any where clause all the rows from the table will be deleted after delete command you need to fire rollback or commit to make the changes permanent or undo it.

Note: this command will cause all the delete triggers to fire on the table.

Truncate

In case of truncate command all the rows from the table are removed and we cannot rollback the changes on the table because no DML triggers are fired on to the table.

Truncate is a DDL command whereas Delete is DML command and truncate is much faster than delete command.

Reason:

When you fire delete command all the data gets copied to Rollback tablespace first then delete operation gets performed that is why when you fire rollback you get data back (the system get it from the rollback table space) . All this process takes time but when you fire truncate command it removes data directly from the table and you cannot retrieve data back from the rollback table space. That is why truncate is faster than delete command.

 

 

 

Concurrency in Java

Concurrent collections are introduced in java 5 to increase scalable multithreaded java applications.

Prior to java 5, if you have to deal with collections in multithreaded application you either used synchronized collections or regular collections.

synchronized

  • HashTable
  • Vector

Regular

  • Regular
  • HashMap

Both of these can be synchronized where HashTable and vector are already synchronized and arrayList and HashMap can be synchronized using utility class under java.collections package.

Before java 5, if one thread is reading a collection resource and the same time when second thread tries to update it then you get “ConcurrentModificationException” which is not desirable.

To overcome this problem, the concept of concurrency come into picture. It provides the following methods:

  • Concurrent HashMap.
  • CopyOnWriteArrayList
  • CopyOnWriteArraySet

ConcurrentHashMap uses concept of lock stripping so while one thread is iterating the collection and another  thread is updating the collection, they will sync up at some point.

Where, CopyOnWriteArrayList  and CopyOnWriteArrayList keeping cloning the collection every time when a write is made to the collection and then at some point they will sync up.

So using concurrent collections, we can use collections across multiple threads.

Note: use concurrent collections when you are using less writes and more read through the collection object.

Disadvantages :

When you are using a lot of writes in application you will clone every time the collection object which will degrade the performance of the program.