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

From: <dgoulet_at_vicr.com>
Date: Thu, 16 Aug 2001 09:56:00 -0700
Message-ID: <F001.0036D747.20010816095832@fatcity.com>

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. 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. 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. 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!

Dick Goulet

____________________Reply Separator____________________
Author: "Miller; Jay" <JayMiller_at_TDWaterhouse.com>
Date:       8/16/2001 9:28 AM

Thanks to you and Kevin!
You'd think I'd have noticed this behavior sometime before, but obviously not.

Hmm, I'm also thinking now about my partioned tables in the datawarehouse, some of which have a global index on them. After exporting and dropping an older partition, I assume all that space is still claimed. That would explain why when I rebuilt the indexes recently I regained a huge amount of space.

Always glad to learn something new :).

Jay Miller

-----Original Message-----

Sent: Wednesday, August 15, 2001 5:42 PM To: Multiple recipients of list ORACLE-L

Index blocks remain when all the leaves are gone. It's late and I'm late for going home but basically, you deleted rows from the table. The index removes those pointers but does not otherwise reorganize itself. After you do a major DML on a table, rebuild the index.

                    "Miller, Jay"

                    <JayMiller_at_TDWater        To:     Multiple recipients of
list ORACLE-L      
                    house.com>                <ORACLE-L_at_fatcity.com>

                    Sent by:                  cc:

                    root_at_fatcity.com          Subject:     Index space not
freed when rows      
                                              deleted?

 

                    08/15/2001 04:48

                    PM

                    Please respond to

                    ORACLE-L

I'm puzzled.
We had a table that hit maximum extents on our QA server (unlike production it isn't cleaned out on a daily basis). I deleted most of the rows, leaving
only a few thousand.
The next day, when inserting more rows I hit the maxextents exceeded error on an index on that table (of course the number of extents hadn't been reduced but I would have thought there'd be plenty of room in 121 existing extents after most of the rows were deleted). I rebuilt the index and it went from 121 extents to 1 extent, resolving the problem, but leaving some curiosity.
It's a primary key sequence based index. Any ideas?

Jay Miller
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: tday6_at_csc.com

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Miller, Jay
INET: JayMiller_at_TDWaterhouse.com

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

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