Re: Checking the rebuildability of an index

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 09 Aug 2002 00:48:19 -0800
Message-ID: <F001.004B0451.20020809004819@fatcity.com>

> Q: What are the chances of knowing whether or not
> subsequent inserts will be able to reuse a slot?

Somewhere between 0 and 100% :-)

For example, take the commit's out of my demo's and the space is not reused as well as it could be. There are so many variations.

This is why I don't believe in rebuilding. Occasionally if I see an index growing in size continuously I'll pop an analyze in and see if its full of holes, and maybe do a rebuild to see what happens...but if it gets full of holes again, it gets taken of the list of rebuild candidates.

Similarly, typical candidates for rebuild are indexes that have a lot of dml activity. Your "average" index normally ends up at around 75% full over time, and a rebuild might take that up to 95% (assumning pctfree 5). If your app does a lot of range scanning, performance may get a boost as a result, but that dml could now suffer due to the need to split blocks. Can that be predicted from stats alone? Unlikely.

If you *know* that query response time is the key for your system, then I would say that a regular 'alter index ... coalesce' would be a cheap and very effective compromise.

hth
connor

• Jared.Still_at_radisys.com wrote: > > Bottom Line: I stand by my original premise -
  > > statistics alone do not determine an indexes
  > candicacy
  > > for rebuild. Some additional knowledge, eg "I
  > know
  > > the rows I've deleted will never be replaced" etc
  > is
  > > needed.
  >
  > Nice test case Connor, thanks.
  >
  > Q: What are the chances of knowing whether or not
  > subsequent inserts will be able to reuse a slot?
  >
  > This would require a history of what is usually
  > inserted, and making an assumption about future
  > inserts based on that knowledge.
  >
  > I'm sure there is a good method for doing this,
  > though I don't know what it is. Even so, it may
  > not be practical to use.
  >
  > It seems like 20% of the effort needed to create a
  > 'perfect' solution would provide the needed
  > information
  > to determine if an index should be rebuilt, and be
  > correct 80% of the time.
  >
  > Jared
  >
  >
  > --
  > 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).
  

  ---

Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"Remember amateurs built the ark - Professionals built the Titanic"

---

Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

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 Aug 09 2002 - 03:48:19 CDT