My guess is that you had no statistics on the indexes previously. However, a better remedy than deleting the statistics on the indexes might be an appropriate setting of the optimizer_index_caching parameter.

You are probably sick of CBO questions, but I'd be grateful for any suggestion or pointers on this! I've encountered a problem with the CBO under Oracle 8.1.6/Solaris 2.6 where it was working very well prior to an export/import cycle but badly afterwards, and after scouring comp.databases.oracle.server, your Q&A's and trial and error experimentation over the last 3 days I'm none the wiser.

The database was exported just prior to running some test update SQL, which didn't produce the desired results. So I deleted all the tables and reimported it and then noticed that queries which had been running very quickly (sub-second) were now taking a very long time (dozens, hundreds of seconds). I reissued ANALYZE TABLE x COMPUTE STATISTICS on each table, but the explain plan showed that whereas before NESTED LOOPS were being used, now HASH JOINS were being used.

A posting on comp.databases.oracle.server suggested disabling hash_join, but when I tried this, as Jonathan Lewis warned, merge join might be used and might be worse (which it was!!!)

Manually supplied hints can be used to select an appropriate query path, but in general, the system produces queries performing 20 - 30 self-joins on this table. Prior to the export/import, the optimizer was doing a beautiful job selecting the best query path using NESTED LOOPS on the condition with the fewest satisfing rows and whittling them down!

I'm very curious as to what has gone wrong, and I'm wondering if you can tell me how the export/import cycle could have so badly effected the optimizer, even after all statistics have been dropped and rebuilt, or have any suggestion for how I can get the CBO again choosing the smallest condition and using nested loops progressively.