This content has been marked as final. Show 3 replies
Santosh Pradhan wrote:It is unsubstantiated Urban Legend that say Index should be rebuilt when BLEVEL=4.
we have table gen_1
We observed that the blevel of index on this table has reached to 4 and when we rebuild an index it goes to blevel 3 .
But when i gather statts on this table ,the blevel for that same index goes to 4 automatically .
3-4 time i observed this ...
Can anyone tell me , whyyyy??
Some indexes are normally that deep & there is NOTHING that can be done to change it.
I would expect that the indexes where you observe this behavior are "wide"; have multiple columns as part of the composite Index.
SB is correct ... you aren't having trouble rebuilding an index ... the trouble is, most likely, that you are trying to rebuild the index. Rebuilding indexes is almost always the wrong thing to do and in your case likely a total waste of time.
In those circumstances where an index has issues COALESCE is generally a far better solution than rebuild.
Unless you can point to a specific metric ... post the SQL and the result ... I'd recommend finding something else to do with your time.
sb92075 wrote:That is true - but in this case the OP says that when he rebuilds the index the level drops from 4 to 3, and that is almost a sufficient argument to do the rebuild if his observation is correct. However he could do with a better measure of the efficiency of his index and how much (benefit if any) the rebuild introduces.
It is unsubstantiated Urban Legend that say Index should be rebuilt when BLEVEL=4.
Having said that I think we need to know how he decides that the blevel of the index is 3 after the rebuild if he hasn't collected stats on it - after all, he says that when he collects stats the blevel changes to 4. Since collecting stats shouldn't (I'd like to say can't possibly) change the blevel of the index I suspect that this claim that the blevel drops to 3 on the rebuild is flawed.