Learn Something Every Day

This once again was brought on by a question on OTN.

In a large migration process one of my steps is to drop and rebuild indexes. Do I still need to ALSO do a ANALYZE INDEX to make sure the index is up to date with data and good to go with the best performance possible? Doesn't drop and recreating force it to be most recent and I would basically be doing same work twice?
This is Oracle 8i I am running on

My initial answer nope, statistics are not created when you create an index.

I posted it, then thought - well, I posted without actually checking - and low AND behold. It doesn't do it for version 8 or 9. But it appears to for version 10.

You learn something every day.

To try it for yourself you can see the following:

• Version 8 Script
• Version 9 and 10 Script
• Version 8 output
• Version 9 output
• Version 10 output

The conclusion is - on oracle 8 or 9, if you drop and recreate an index the statistics are not gathered at create. In V10, the statistics are gathered at create. I guess I will have to stuff this away for an AskTom question because I can't find any documentation relating to this. But it is something difficult to search for.