Re: Is space released after deletes?

Carlos Augusto Leite Netto <cnetto_at_bestway.com.br> wrote:

> >
> > For a table, Oracle will only reuse the deleted row space once the blocks
> > freespace/percent used has fallen below the "pctused" value. Once it does
> > the blocks address is inserted into the beginning of the freelist chain
> > for the table so that it will be reused before a new/never used block is.
 

> Let's talk about this. The text above is right according to the manual.
> But I have many customers where it's not happening.
 

> For example, I have many tables where I do:
 

> select count(*)
> from table;
 

> and it returns 0 (zero). It stands that the table has now rows. But this
> select takes minuts to run and the table uses many blocks of my database.
> After doing "truncate table", the same select starts running very quickly.
> (When I did the testes I was the only user connect to the Oracle).
 

> Does anybody here know anything about this? Any bug? All pctused are Ok,
> etc. Nothing trivial.

PCTUSED and PCTFREE apply to the contents of individual blocks and the number of rows you can fit in these blocks.

When you DELETE all rows from a table, those blocks are still allocated and if you SELECT COUNT(*) from that table Oracle will search through the allocated blocks looking for rows, hence the length of time.

Doing a TRUNCATE (unless you use the REUSE option) will deallocate all blocks so that only the original INITIAL extent is left. Hence, the query will be much faster.

This behaviour has been the norm since Oracle V5 and will be in the future, AFAIK. This is also consistent with everything which has been said so far in this thread.

--
Peter Moore.
DBA, IS Ops,
Sequent Computer Systems Ltd, Weybridge, UK.