Like this:

Related

Given 471,150 LF_ROWS, I wouldn’t say that 1248 DEL_LF_ROWS is particularly large. About a quarter of a percent.
But with around 15 bytes needed for each index leaf entry (the sequence number and rowid), you should be able to fit 500 in a block. 500,000 would fit in about a thousand blocks. So the 2100 LF_BLKS before the rebuild is more notable. The 800 after the rebuild is considerably more compact.

By removing half the leaf blocks, the cost of an index range scan is halved.
Interestingly, the 735 cost for the query after the rebuild is higher than the 704 cost for the full scan before the rebuild.

Personally I would have gone for getting histograms on the index stats rather than a rebuild. The optimizer estimates are still way off based on the column skew.

It’s also somewhat interesting to me that before the rebuild the index had got the 471150 rows that Gary refers to above, but afterwards it has lost 26845 rows entirely ( 5% of the total rows in the index.) If this is a production system perhaps something else is going on at the same time? If the distribution of values shown is typical (most rows have 0 and only a few have a non zero value for the test_id column then it would be a classic candidate for histograms.

The numbers for leaf rows and costs are exactly consistent with the table having the 444305 rows after the validate structure divided amongst only 7 distinct values, rather than the 9 before. That’s a fairly significant change with data as skewed as this (and explains the cost difference Gary observed)