It's important to have statistics on all tables for the CBO (Cost Based
Optimizer) to work correctly. If one table involved in a statement does
not have statistics, Oracle has to revert to rule-based optimization for
that statement. So you really want for all tables to have statistics right
away; it won't help much to just have the larger tables analyzed.
Generally, the CBO can change the execution plan when you:
1. Change statistics of objects by doing an ANALYZE;
2. Change some initialization parameters (for example:
hash_join_enabled, sort_area_size, db_file_multiblock_read_count).