Oracle-L: Re: rebuilding indexes

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Fri, 31 May 2002 09:32:25 -0800
Message-ID: <F001.00471202.20020531093225@fatcity.com>

Matt,

Storage parameter changes? Do you specify storage at the index or tablespace (or top partition) level?pctfree go up? initrans go up? bigger INITIAL or NEXT?
did you build it the first time using parallelism and rebuild using single thread? When a PQ processes is used to build an object it uses its own extent, and then *deallocates the extra space* at the end. If you will notice, when you build using PQ your segments have wacko sizes, and that is why.

If you rebuilt using a single process, it could be that you have a full allocation of space, some empty.

3) more data? did you "alter unusable", load, and rebuild?

4) have you changed the minimize_records_per_block parameter and the index is a bitmap?

5) has your data distribution changed? bitmap indexes tend to get bigger as your data moves towards high cardinality.

6) has this index been eating too much sugar again? sometimes, this can cause an index glucose spike and tend to make it store more fat in the leaf blocks, especially around the middle of the index, but sometimes on the hips too. Perhaps you are not doing DDL on the table enough, and so the index is not getting enough exercise.

hth,

jack silvey

> --- "Adams, Matthew (GEA, MABG, 088130)"
> <MATT.ADAMS_at_APPL.GE.COM>
> wrote:
> > Under what conditions would an 'alter index ..
> rebuild'
> > actually case the size of the index to increase
> > by about 12 percent?
> >
> >
> >
> > ----
> > Matt Adams - GE Appliances -
> matt.adams_at_appl.ge.com
> > Contrary to popular opinion, Unix is user
> friendly.
> > It's just particular about who it makes friends
> with.
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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).

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.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).

Received on Fri May 31 2002 - 12:32:25 CDT