Re: Index rebuilding (was: change index in tablespace)

From: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Thu, 16 Dec 1999 07:02:59 +0100
Message-ID: <945324234.3517.0.pluto.d4ee154e@news.demon.nl>

Index deteroriate by deletes but also when inserting at one logical end of the index only, for instance when a date is involved, only recent dates. This will eventually force a new level to be added to the B+-tree. The tree will be rebuilt, so will be correct and balanced using alter index rebuild. Apparently you didn't specify any storage parameters. In this case Oracle will more or less accurately compute the size of the index.

Hth,

--
Sybrand Bakker, Oracle DBA
Paul Bennett <bennett_at_cc.gatech.edu> wrote in message news:385851E9.5F21CAB9_at_cc.gatech.edu...
> Does it make sence that rebuilding an index would cause the new rebuilt
> index to fit just inside the initial extent? The index is on a table
> that doesn't get very many deletes, just inserts. Why would the size of
> the index decease so dramatically. Why does it just happen to fit
> inside the initial extent? Also, there is only one extent, the size is
> shown to be larger then the initial extent size.
>
> Does not compute.
>
> Thanks.
>
> -- Paul
>
> Here is an example:
>
> SEGMENT_NAME
> PARTITION_NAME SEGMENT_TYPE
> TABLESPACE_NAME BYTES BLOCKS EXTENTS
> INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA FREELISTS
> FREELIST_G BUFFER_
> --------------------------------------------------------------------------

---

> ------------------------------ -----------------
> ------------------------------ ---------- ---------- ----------
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- -------
> CM_UPDATED_DATE
> INDEX LTAPP_INDEX 3555328
> 868 112 1347584 1048576 1 249
> 0 1 1 DEFAULT
>
>
> After alter index cm_updated_date rebuild;
>
> SEGMENT_NAME
> PARTITION_NAME SEGMENT_TYPE
> TABLESPACE_NAME BYTES BLOCKS EXTENTS
> INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA FREELISTS
> FREELIST_G BUFFER_
> --------------------------------------------------------------------------

---

> ------------------------------ -----------------
> ------------------------------ ---------- ---------- ----------
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- -------
> CM_UPDATED_DATE
> INDEX LTAPP_INDEX 1351680
> 330 1 1347584 1048576 1 249
> 0 1 1 DEFAULT
>
>
> Xuequn Xu wrote:
>
> > alter index <your_index_name> rebuild
> > tablespace <your_new_tbs>;
> >
> > "Robert" Xuequn Xu, Oracle DBA
> > Caltech, Pasadena, CA 91125
> >
> > desertflowerln_at_my-deja.com wrote:
> > : How do I move indexes from one tablespace to another, without
> > : exporting/importing, in Oracle 7.3.X
> > : Thanks!
> >
> > : Sent via Deja.com http://www.deja.com/
> > : Before you buy.
>
Received on Thu Dec 16 1999 - 00:02:59 CST