RE: Rebuild Index?
Date: Fri, 28 Feb 2014 07:47:50 -0500
Message-ID: <1f4e01cf3483$4acc3210$e0649630$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Karth Panchan
Sent: Friday, February 28, 2014 7:15 AM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 28 2014 - 13:47:50 CET