I have a large database. The optimiser is currently set to 'RULE'.
Should I be using the cost based optimiser?
I would be grateful if anyone could explain a bit more about the optimiser or point me to some relevant links or articles on this subject.
If you switch the Optimizer to CHOOSE then never, never collect statistics for every Table.
You will wonder but the CBO in some Cases decreases the perfomance.
Go table by table and most by tables you like to use features which needs CBO;
The CBO is not bad but you have to think a lot how you create the statitistics, when to create histograms, when to compute when to estimate, how often per week/month ...
i think your way is the best!
So the (professional) SQL-Developer can decide with a Hint when to use CBO.
The only thing is that you define an analyze-Subsystem which anlyze the objects for you in a CPU-NonWaisting and exact way.