Oracle-L: RE: rebuild index -initial extent

From: <Paula_Stankus_at_doh.state.fl.us>
Date: Thu, 31 Jul 2003 02:34:35 -0800
Message-ID: <F001.005C7F62.20030731023435@fatcity.com>

If this is true:

The rebuild creates a new temporary segment that is the same size as the required extents in the old index. If there is insufficient space to create this temporary segment you get this error.

It doesn't reuse the existing space the index occupies but builds a second identical index then renames and drops the old one.

Then rebuild will take longer as volume of data increases and more space will be required. Where is the temp.. segment created? In the old tablespace, the new tablespace (if you are moving it) or in memory or .....in memory then ...??

I did the following in Oracle RDBMS 9i:

SQL> alter index xsc_uk rebuild tablespace ax_le_small storage (initial 128K 2 next 128K);
SQL> select initial_extent,next_extent,index_name from dba_indexes 2 where index_name like 'XSC%';

         65536      131072 XSC_CLNT_FK_I
         65536      131072 XSC_PK
        131072      131072 XSC_UK
____________________________________________________________________________

As I wish to use uniform extent sizing and I was given an import that does have that. I am a little concerned about the initial extent changing - what if there is data in the index? - how it could possibly deallocate space if you wish to have a smaller extent size. It was very quick. Did I really end up with new extents for XSC_UK each 128K????

--

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

Author:
INET: Paula_Stankus_at_doh.state.fl.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Thu Jul 31 2003 - 05:34:35 CDT

RE: rebuild index -initial extent - magic??!!