| 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: RE: index rebuild
> Something to keep in mind when contemplating a rebuild is that (unless
> you do LOTS of index fast full scans) performance will NOT be
> affected unless
> you can reduce the BLEVEL of the index.
> If you
> do a rebuild, and the BLEVEL doesn't change, you've probably wasted
> time and
> resources.
If your index on volatile data is more dense after a rebuild (which it usually is) then your buffer cache will be better utilized, meaning that less PIOs have to be done, resulting in improved overall index read speed. Also, scans on index may require less LIOs when less blocks have to be visited to get all required keys.
Coalesce may help out here in some cases, but not in all ones, since coalescing only coalesces adjacent leaf blocks if their contents will fit into one block (taking the pctfree into account). Also, coalesce doesn't coalesce branch blocks nor leaf blocks under different branches of a tree.
When measuring the effect of an index rebuild, it's important to concentrate to both index querying and updating speed, a freshly rebuilt index updates may require more efforts for block splitting for example (depending on data usage pattern and pctfree setting).
Tanel.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 18 2004 - 23:25:48 CST
 |
 |