Re: Index space not freed when rows deleted?

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