Re: Re:RE: Index space not freed when rows deleted?

Comments in line

Jonathan Lewis

Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 16 August 2001 18:38

|Jay,
|
| Indexes while great performance enhancers are maintenance
problems. Have
|been and always will be. The problem stems from the way that the
B-Tree index
|structure works. When the index is created the entry point is
equidistant from
|both ends. Sooner or latter one side gets longer and deeper than the
other
|tossing the index off balance.

The b-tree mechanism used Oracle is a 'balanced b-tree' no leaf is more than one level deeper in the index than any other leaf.

| Now this is bad enough, but
as rows get deleted
|their place in the index just gets nulled out, but not released and
reused
|unless you have that exact same value to put back.

No. the index entry is marked as deleted, just as rows in a table are marked as deleted, which makes space available for the next transaction that tries to use that block. Any index entry which is appropriate for that block (which means greater than or equal to the lowest value in the block, and less than the lowest value in the next block) will go into that index.

|

Now to make matters worse,
|the Oracle Kernel in it's infinite wisdom and desire to make matters
fast sooner
|or latter realizes that a block in the index is completely empty.
One would
|expect that that block would then end up on the free block list and
get placed
|somewhere else in the index, but NO, what happens is that Oracle
routes itself
|around the empty block leaving it in limbo.

No. The block is structurally 'in the index with deleted rows' until clean-out.
After the next clean-out it is still structurally in the index as far as view
INDEX_STATS is concerned, but it is also on the index free-list and can
be reused when a leaf or branch block split requires an empty block.

| Dump a
number of index blocks to
|trace & you'll see what I mean. So here we have this huge index
(disk space
|wise that is) with only a handful of rows therein. What can you do,
only two
|things, use 'alter index rebuild' or drop and rebuild it. In either
case be
|prepared to reclaim some space, often a whole lot more than you
expected!
|

The commonest reason for a vast index with a few rows is the FIFO index
based on incrementing values where 'old' data has low values and ''new'
data has high values. Oracle does not coalesce blocks which are nearly empty, so unless ALL the rows from a block are deleted then the block cannot become free, and even then there has to be a post-transaction clean-out which allows Oracle to clean-out the last row marked as deleted. All examples I have seen of 'huge but empty' indexes have been indexes where perhaps one row in every thousand get into an 'exception' state and therefore stays in the index - in which one row = one held index block.

In Oracle 8.1.7, the COALESCE option for indexes addresses this issue.

|Dick Goulet
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).