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
