Recover that lost space

Do you know what happens when you issue a DELETE? Have you wondered what happen after COMMIT? ... well, I might say that there is life after delete for all those blocks that belonged to the data recently deleted.

I'll present the following test case to show you how this works:

First we are going to create our test environment, borrowing a table from the Examples OE schema, creating it in the SCOTT schema.

SQL> alter session set current_schema=SCOTT;

SQL> create table pd as select * from oe.PRODUCT_DESCRIPTIONS;

Let's see how many blocks our new table PD is using

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)
-------------------- -----------
PD                           384

Now we're going to see how it's structured the table:

SQL> select column_name,
2: substr(data_type,1,20) as dt,
3: data_length
4: from all_tab_columns
5: where table_name = 'PD';

COLUMN_NAME                    DT                   DATA_LENGTH
------------------------------ -------------------- -----------
PRODUCT_ID                     NUMBER                        22
LANGUAGE_ID                    VARCHAR2                       3
TRANSLATED_NAME                NVARCHAR2                    100
TRANSLATED_DESCRIPTION         NVARCHAR2                   4000

That structure yield us a theoretical row length of (22+3+100+4000) = 4125, which if used fully will give us just 1 row per block (see below for DB blocksize). But , rarelly a NVARCHAR2 is fully used.

SQL> show parameters db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

We know there is data and how it's structured, but we need something to erase... the following may be politically incorrect but is only for instructional purposes:

SQL> select language_id, count(*) from pd group by language_id;

LAN   COUNT(*)
--- ----------
US         288
IW         288
TR         288
...
S          288
SK         288
ZHS        288

30 rows selected.

We're going to delete all the 'S' language registers, I don't know what means 'S' and I'm sorry if I hurt nationalistic feelings, remember this is hypotetical.

SQL> delete from pd where LANGUAGE_ID = 'S';

288 rows deleted.

SQL> commit;

Commit complete.

That was the death sentence for those rows, now will see what happened with the blocks assigned to PD.

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)
-------------------- -----------
PD                           384

Wow! the table still has the blocks assigned, and will not return them until a TRUNCATE, DROP ... or ALTER TABLE is issued. Yes! an alter table will return the extents to the tablespace free pool.

SQL> alter table scott.pd deallocate unused;

Table altered.

SQL> column segment_name format a20
2: select segment_name, sum(blocks)
3: from dba_Segments where owner = 'SCOTT' and segment_name = 'PD'
4: group by segment_name
5: order by sum(blocks) desc;

SEGMENT_NAME         SUM(BLOCKS)
-------------------- -----------
PD                           376

There it is!!! the blocks that contained all the 'S' language registers, now don't belong to the table PD.

At first you may be tempted to include a periodic ALTER TABLE...DEALLOCATE UNUSED in order to recover all that 'wasted' space, you must be carefull when evaluating this, because there are tables that get data erased and never or rarelly get new data, them are perfect candidates for this.

On the other hand, tables with heavy delete activity won't benefit too much because of the overhead related to update the internal tables that keep track of blocks and extents. What is 'heavy' activity, depends on the deleted rows/total rows rate, and you must develop a maintenance criteria to exclude tables.

You're welcome to send your comments... I hope this help you

Please, before you go, don't forget to vote the poll regarding the content of this blog, thank you!
Subscribe to Oracle Database Disected by Email