Sorry for taking so long to get back on this - I've been
a bit busy for the last week.

Simple solutions are desirable - and there's no such thing as
"too simple", although there is "too simple-minded".

It's an interesting set of figures on the rebuild - especially
with those little spikes.  I'm guessing that there's been a little
bit of activity that would explain why you've got a few blocks
with about 113 rows in - those look like the result of a few
leaf block splits.

Could I guess that the leading column of this index has a few
(perhaps just 3) values which are particularly popular - perhaps
it's a single column index with a lot of skew, or a multi-column index
with a skew on the first column.  That might explain the spikes at
199, 205, and 210 rows per block.  If that's the case, then compressing
on that column would be sensible at any time.

Since you've got 176 rows reported before the rebuild that suggests
you've got some blocks with 290 rows per block -  which is odd
because that doesn't show up after the rebuild.  (At a default 10%
free on a rebuild that 290 peak ought to drop to about 261, and
your figures are way below that - do the counts start to taper
off at about 230 rows per block so that blocks with more rows
are just the odd one or two ?)

The post-rebuild figures would encourage me to look closely at
the index because it isn't a "boring, random" index; but (if I hadn't
seen the post-rebuild) I would have looked at the pre-rebuild
figures and decided that I wasn't really going to see a DIRECT
performance improvement from the rebuild - so I would only
consider rebuilding it if
    a) It was extremely popular
and I was fairly confident that
    b) the 60,000 (call it 40,000 after initial degeneration) drop
        in the block count meant I would get a constant saving in
        the buffer cache that paid for the work and time spent in
        rebuilding.
However, given that blocks seem to start splitting soon after the
rebuild I might then take a little care to balance the frequency of
rebuilds against the pctfree I set so that I managed to rebuild
before any serious amount of block splits started to occur.

Bottom line - if you see a performance issue related to db file
sequential reads, and if the segment statistics (v$segstat) highlights
a particular index as being a major culprit, and if the query shows
the index blocks to be thinly populated  (spread like yours, or with
a very long near-empty tail that you think is being scanned) then
consider rebuilding a thinly populated index, and coalescing an index
with a long tail.

Remember: a B-tree index on randomly arriving data will spread
to an average 70% utilisation.  A rebuild index will (by default) run
at 90% utilisation.  Therefore rebuilding indexes which show a 20%
drop between prod and test is too simple-minded.  An index that
shows an interesting pattern before or after rebuild may be worthy
of special consideration.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html