Re: Does ANALYZE tables/indexes need to be executed periodically ?!

Yes and no. You should analyze volatile tables and indexes regularly, because statistics on these objects are collected at one point in time. A high volume of updates, inserts and deletes will outdate statistics collected (or estimated) on these objects. Objects should also be re-analyzed when a table structure is altered or indexes are added or dropped.
Static objects, such as look-up tables/indexes, usually do not need to be re-analyzed.
On the same topic, setting the optimizer_mode to "choose" will do nothing unless objects have been analyzed. Oracle will "choose" rule-based optimization if there are no object statistics available. This is why your performance has improved considerably after analyzing your objects. Neat stuff.

'til l8r,
E. Received on Sat Dec 20 1997 - 00:00:00 CST