You Asked
Hi Tom,
My query is regarding to find the candidate index to rebuild. Please do not get annoyed for this dead old question.
I know you are against about index rebuild on fix interval, you said index will grow again to same way. Rebuilding will generate more redo, more resource consumption ..
But I have seen sometime good performance gain after rebuilding indexes. we have hard time to find the real candidate index which will help to boost the performance. So normally we do rebuild all the indexes but it's take too much time.
Further, I have searched on internet and seen suggestions for finding the index
1 - to see if Blevel/height is more than 3 or 4, ( i have seen no benefit )
2 - validate index and see if there are high no. of rows got deleted ( it's time consuming for a large database )
What would be your criteria for really huge database like 23 TB with thousands
of user online doing mix ( oltp/dss ) kind of activities.
I just want to find out the indexes which required rebuilding.
Please suggest what kind of house keeping scripts you would like to put
if you manage this kind of database.
Thanks & Regards
and Tom said...
... But I have seen sometime good performance gain after rebuilding indexes. ...
And you will not find anything on this site to the contrary. No where, not anywhere, will you see "do not ever rebuild"
You will see over and over
"do not rebuild everything willy nilly with no cause, no reason, no thought - you can cause more harm than good and MOST of the time you effect nothing positive (but spent a lot of resources doing so)"
The problem with #1 is "what if the height stays the same" - doh
The problem with #2 is that deleted leaf rows is not entirely "reliable". Just because you delete does not mean that deleted leaf rows will increase - you could do a mass purge and then find del_lf_rows to be 0.
There are two types of indexes out there in general, basically
a) those that are on data that arrives randomly - like on last_name. YOu do not hire everyone whose name starts with 'A' on a given day, and then 'B', and then 'C', they arrive randomly. You insert all over the place in this index (and update and delete). Your modifications are distributed all over the place
These indexes rarely, if ever, need to be looked at. If you fire KYTE, you will hire KING later and KING can and will reuse KYTE's slot in the index. The index will tend to be 20-40% empty at any point in time probably - but that is ok - because if you rebuild (and make it 10% empty), it'll just get fat again - the data updates are chaotic - it'll never "stay small"
b) those that are on data that arrives in order (think sequences, think dates populated by sysdate). Here, if you delete a low value on the left hand side of the index - we'll never be able to reuse it since the only values that come into this index go on the right.
These indexes could sometimes benefit from a COALESCE (always online, no two times the storage, no change of ora-8103 in other sessions) if (and only if) you delete a lot, but not all, of the old values over time. If you don't delete most, but not all, you don't need to rebuild. If you do a purge of large contiguous sets of old values - making a leaf block empty - you probably do not need to rebuild (we'll move that leaf block)
it is only when you remove many/most of the old values - leave some stragglers behind, and the index is on a increasing (or decreasing) value.
So, I don't use a script, I know what indexes might benefit based on.... Knowledge of the data. Indexes on sequences/dates whereby you delete many/most but not all of the old values.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment