Quote Originally Posted by abhaysk
i still wonder how the heck can rebuild help in query completing faster than without?
Here is a scenario, assume that...

a) You have a large table where heavy random delete happens.
b) You have a composite index where a sequence or a date is the first column of the index.
c) Your queries have faulty predicates where WHERE clause is using an incomplete set of the columns of your composite index.

As a result of this scenario, over time you are going to find that...

1) Because of items (A) and (B) your index is getting less and less dense e.g. less and less active keys per block as time goes on.
2) Because of item (C) your queries are forced to do range scan on the index e.g. scanning more and more blocks as time goes on.

In this scenario your queries are going to slow down performance over time, just a bit worst every single day.

When you rebuild your offending index guess what? index gets more dense e.g. more active keys per block then... index range scans are shorther and voila!... queries run faster.

PS: Please don't come back with the leaf-blocks are always in balance theory; because of (B) followed by (A) this is not true for this specific index.