RE: Rebuild Index?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <keyantech@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 28 Feb 2014 07:47:50 -0500
Adopting a strategy of chronically rebuilding every index that exceeds the
size of the table it indexes is a bad idea.
Usually it gains nothing sustainable, in the case taking the air out leads
to subsequent extraneous leaf block splits it can transiently slow things
down a bit, and even being able to do it on line it still represents a load
against normal processing while it is being done.
It may be useful if since it was created the table reached a much higher
size level than it currently has (or are likely to have again soon).
It may be useful if some time in its history it was subject to a monotonic
direction of new keys that resulted in a suboptimal branch and leaf
structure.
If you have a few such indexes and lack the resources to do the metrics and
can tolerate the possible temporary performance degradation, it may be
cheaper to just rebuild them once and watch the behavior than to really
understand what you are doing.
But even if this leads to a dramatic reduction in size and an improvement in
performance for one or a few indexes you choose, please do not jump to the
conclusion that is a good idea for everything.
In addition to the very good sources of discussion about why and why not you
cited below, Richard Foote's blog is full of pros and cons, whens, whys, and
why nots regarding indexes.
You'll want to dig in specifically about bit maps if you have them. When bit
maps are a good idea they are very powerful; when they are a bad idea they
can be really nasty.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Karth Panchan
Sent: Friday, February 28, 2014 7:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Rebuild Index?
Everyone
We have table with lot of insert/update/delete every day. High OLTP
application in 11g R2 on Linux.
To gain performance my teammate recommend Rebuild index.
His assumption Index size should be less than table size. Other wise we need
to rebuild index.
Is that correct? Want get your thoughts.
I am skeptic after Reading Asktom and Jonathan Lewis blog. Both of them
claim need good metrics and it is last option.
Thanks
Karth
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
