| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Checking the rebuildability of an index
Actually, I do the 'validate structure' as well, and neglected to include
it in my previous post.
Jared
"Rajesh Dayal" <rdayal73_at_techie.com>
Sent by: root_at_fatcity.com
08/06/2002 02:23 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: Checking the rebuildability of an index
Hi Jesse,
I would like to differ from the concept of rebuilding the indexes based on BLEVEL. This could be misleading sometimes.
One should always check the index_stats table after "analyze index <index_name> validate structure ;" command. If you see the PCT_USED column to have a good value (for example > 85 - 90 %) then it is extremely unlikely that rebuilding the index will bring down the value of DBA_INDEXES.BLEVEL or INDEX_STATS.HEIGHT ( to be precise, DBA_INDEXES.BLEVEL = INDEX_STATS.HEIGHT - 1). So it looks like, INDEX_STATS.pct_used or "DEL_LF_ROWS / LF_ROWS) > 20%" is "much better" indicator than BLEVEL.
If you have a high value of PCT_USED ( around 90) but still wanna have low value of DBA_INDEXES.BLEVEL or INDEX_STATS.HEIGHT then you should move to a larger block size. Of course this is much easy in case of 9i where in the same database you can have diff tablespaces of different block size. Another crazy way would be to reduce the no of columns from the index definition :)
Answer to second Part is a bit tricky or difficult rather. You should remember that during rebuilding, the temp space used will be from the same tablespace where the index is present. This really looks crazy to me, but that's the way it is. One scene I remember, while rebuilding an index of
115 Mb even a free space of 500 Mb was falling less.................Accordingly you decide the space requirements of rebuilding indexes.
All comments are most welcome.
Hopw this helps,
Rajesh.
PS: I was confused for a while whether the BLEVEL is a column of DBA_INDEXES or INDEX_STATS after reading Jared's mail ;)
> Hi all,
>
> On our ERP DB, 8.1.7.2.0 on HP/UX, I'm finally able to implement an
> automated index rebuild job. I've got two questions:
>
> First, I've planned to use the gauge of b*-tree HEIGHT and "DEL_LF_ROWS
/
> LF_ROWS) > 20%" from the SYS.INDEX_STATS view to determine what indexes
to
> rebuild. I've read in "Gaja 101" that the leaf block density should
also be
> considered. Is this LF_ROWS / LF_BLKS (from INDEX_STATS)? If so, are
there
> any guidelines as to what the density should be? Or is it only the
> percentage decrease over time that matters? And if it is, what
percentage
> decrease over what period of time should be considered? This would seem
to
> be very installation-dependant, but I'm just hoping for general
guidelines
> for a starting point so I'm not rebuilding our largest indexes weekly if
> it's not needed.
>
> Second, how does one determine if there's enough room to REBUILD ONLINE
an
> index? Other than comparing MAX(BYTES) to the INITIAL clause of the
index,
> that is. I've looked in Metalink, "Gaja 101", and several websites, but
> nothing jumped out at me.
>
> TIA!
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI
USA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.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).
>
-- __________________________________________________________ Sign-up for your own FREE Personalized E-mail at Mail.com http://www.mail.com/?sr=signup Get 4 DVDs for $.49 cents! plus shipping & processing. Click to join. http://adfarm.mediaplex.com/ad/ck/990-1736-3566-59 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal INET: rdayal73_at_techie.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: Jared.Still_at_radisys.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 Tue Aug 06 2002 - 13:04:41 CDT
 |
 |