RE: Index space not freed when rows deleted?

From: Miller, Jay <JayMiller_at_TDWaterhouse.com>
Date: Thu, 16 Aug 2001 09:24:55 -0700
Message-ID: <F001.0036D660.20010816092816@fatcity.com>

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