CODEWITHSUNDEEP
mysqlsql-serverdatabasesql-server-2008
Randel Ramirez
Randel Ramirez

Reputation: 3761

Does deleting a row in a database makes that database free up space?

Inserting a row in a database adds up data to it. Does deleting a row free up space in that database? Or will the row just be deleted without changing the amount of used space?

Upvotes: 2

Views: 888

Answers (3)

roymustang86
roymustang86

Reputation: 8633

Ask Tom says for Oracle :

When you delete the data from the table -- the blocks will go onto the freelist for that table (assuming the amount of space on a block that was freed fell below the pctused). These blocks will be used for subsequent inserts and updates into this table.

When you delete data from the index -- if the block the index entry was on is now "empty" -- that block will go back onto the freelist to be used anywhere in the index struct. Else that block stays where it is and data that would naturally go onto that block (because of where it is in the b*tree) will go there.

Space is effectively reused when you delete. Your database will not show any new free space in dba_free_space -- it will have more blocks on freelists and more empty holes in index structures.

Upvotes: 2

The Nail
The Nail

Reputation: 8500

When you use Oracle, space from removed rows can be reclaimed immediately using specific commands. Otherwise it will stay reserved for new rows, as @oymustang86 mentions.

Upvotes: 3

MatthewMartin
MatthewMartin

Reputation: 33193

For SQL, it depends on from whose standpoint. For example, if you can insert a million rows before running out of disk space, if you delete half of them, then you have space to insert a half million more again. (Ignoring transaction logging-- just imagine that the logging is done elsewhere)

The data file, after it has grown, will not shrink. So the drive space used by the data file isn't available to the OS or any other application. Until, a database shrink operation is done. That re-arranges the data inside the file to free up space on the drive for the OS or other application to use.

Upvotes: 2

Related Questions