Да я же говорю, что известная фигня. На больших объемах он тупит.
И также не нужно считать оптимизатор "умнее" себя. Он не умнее и не глупее, он алгоритм. И где-то в его логике косяк.
А косяк заключается в том, что план он выбирает исходя из cost оценки. Вот только проблема в том, что оценка не совпадает с действительностью. На больших относительно кэша таблицах далеко не совпадает. Он считает, что без индекса быстрее, а по факту получается не так. И каждый раз не так.
Я уже давно наигрался на своих мега-табличках и помогает только force index. Судя по всему у оценщика нехватает разрядности во внтуренних алгоритмах или еще чего-то. Ведь граница в 20% - это подходит не ко всем таблицам. А если таблица 200млн? Лучше было бы, если б можно было оптимизатору явно давать информацию о стоимости IO диска и памяти. И чтобы он учитывал это.

Вот с этой опцией optimization_goal очень опасно играться. Может выйти боком совершенно в другом месте. Причем может произойти через год, и будете ломать голову с другими запросами почему вдруг перестало работать. Я такие грабли уже имел в 2005-м.

Ggg_old,

А что это за CALIBRATE DATABASE такое? Не могу в реферансе найти... Может оно тут как раз поможет?

Судя по описанию, это то что нужно:
You can also use ALTER DATABASE to perform recalibration
of the I/O cost model used by the optimizer. This updates the Disk Transfer
Time (DTT) model, which is a mathematical model of the disk I/O used by
the cost model.
Очень любопытно, повлияет ли и как на ситуацию у автора.