discussing some technical aspects covering tools, frameworks, technologies etc (the areas where i m involved and getting experienced) - feel free to discuss!

Friday, February 23, 2007

Tip to increase the efficiency while deleting huge volume of rows

This tip would be very much useful when you have enormous rows in a table and you are trying to delete/remove the rows using the usual, "DELETE FROM <tableName>;" statement.

You may ask whats the consequences of using the DELETE statement and thats what its purpose is. But, not just deletion, for that matter any DML statement will make use of something called TRANSACTION LOG which is nothing but the buffer/temporary storage where all the data would be kept until there is an explicit COMMIT or ROLLBACK.

Once a Commit is issued, the data kept in the Transaction Log is written into the database files in its own appropriate format (.dat for data files, .inx for index files in IBM DB2 database - which may vary in Oracle, MySQL, SQL Server etc..).

When a rollback is issued, the data in the temporary Transaction Log is removed and thereby no state change happens to the data files.

Moreover, its gonna be time consuming wherein the time is directly proportional to the dense of data.

When you deal with large number of data to be dealt with, be it inserting, updating or deleting, you might end up in something called, "The Transaction Log is FULL" while doing the operation. Most of us might not have encountered this situtation as we do just deal with 100s or max 1000s of data. People who are dealing with lakhs, millions of rows might have been aware of this. Say, who are in Data Warehousing, Business Objects etc.

When we encountered the situation, one of my teammates in my client place (Yadati Ravishankar) had come up with a query what he found out in googling. This query does the magic by just clearing up the table with very very less time compared to that of plain old DELETE statement.

Here you go...

"ALTER TABLE [<SchemaName>.]<tablename> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;"

This query does alter the table with empty rows but while doing the same it requests the db engine not to activate the Logging. Thereby it is able to get the request serviced within seconds!!

Try with huge data buddies..then only you can make out the difference!!

Good luck!

No comments:

About Me

ஏதோ பிறந்தோம், ஏதோ வாழ்ந்தோம் என்றிருப்பதல்ல வாழ்க்கை! எப்படி வாழ்ந்தோம் என்பதும் ஒரு அங்கம். வாழக் கிடைத்த வாழ்க்கையில், ஒரு சிலருக்காவது வசந்தத்தின் முகவரியை அறிமுகம் செய்தோமேயானால் அதுவே வசீகரத்தின் வனப்பைக் கூட்டும்!