Great Tips on Reusing Space after deletion of database data!!

 Author: Kim Powell

Page: 1 2 3

In this article I will discuss some great tips about reuse of space when data space is created due to deletion of data. The attempt to reuse space will most probably affect the decision when the rebuild or re-organize operation should be performed.

Whenever you delete a data from the tables, some space is created. However this space gets reused by the system when you subsequently insert the same data.

Create Table:

First we create a table ‘MYTABLE' with two columns ‘MYCOL1' AND ‘MYCOL2'

CREATE TABLE MYTABLE (
MYCOL1 INT,
MYCOL2 NVARCHAR(800)
);

Analyze Meta-data with no data inserted:

Now we will view the meta-data of that table. As we have not inserted any data into table so when we query the DMV, we will get 0 value.

SELECT alloc_unit_type_desc, page_count, avg_page_space_used_in_percent, record_count, index_level, index_depth FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.MYTABLE'), NULL, NULL, ‘Detailed');

   alloc_unit_type_desc  page_count     avg_page_space_used_in_percent     record_count   index_level     index_depth
   ----------------------------    -----------------   -----------------------------------------------         ------------------   ---------------     -----------------
   IN_ROW_DATA                      0                                                0                                                 0                    0                             0

Data Insertion:

Now we insert 800 records in the table.

SET NOCOUNT ON
GO
DECLARE @value INT
SET @value = 1
WHILE (@value <= 800)
BEGIN
INSERT INTO dbo.MYTABLE (MYCOL1, MYCOL2) VALUES (@value, REPLICATE('A', @value))
SET @value = @value + 1
END

Analyze Meta-data after insertion:

Now we will again view the meta-data of MYTABLE by running the same query.

SELECT alloc_unit_type_desc, page_count, avg_page_space_used_in_percent, record_count, index_level, index_depth FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.MYTABLE'), NULL, NULL, ‘Detailed');

   alloc_unit_type_desc  page_count     avg_page_space_used_in_percent     record_count   index_level     index_depth
   ----------------------------    -----------------   -----------------------------------------------         ------------------   ---------------     -----------------
   IN_ROW_DATA                      46                                 71.4184457622931                         800                 0                        1            Continued...

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Database Tuning with Automatic Segment Space Management!!
   The Power of Oracle Data Integration Tools!!
   Oracle Recovery from an Unplanned Outage!!
   Database Guide: Beware of Fetch across Commit!!