Re: Why are optimizer hints required?

Hi Matt,

Time for a new DBA.

Is using hints really the easy way out? Hardly. Is reading from an index always better than a full table scan? Nope. Could it be possible that maybe, just maybe, the DBA can spend a little more time in figuring out the best way to analyze the tables and indices?

For argument's sake, let's assume that the development and production instances are similar in volume, if not identical. Then, say the hints put in place work well, both in development and production. In other words, the DBA is happy because the optimizer favors index scans over full table scans. But then, what happens if at some point in time, the performance of the applications degrade because

1. The data has changed, and the hints are now inappropriate.
2. The optimizer can't help because the query has hints?

Let's try the easy route that the DBA proposes. First of all, how does he know which path to take for each query? Then, when he figures out which hint(s) to use, how did he come to his conclusions? Apart from that, does this mean you have to go back and change your code with different hints each time a problem occurs in production? This doesn't sound so easy.

In a nutshell, the easy way is to use the Cost Based Optimizer. It's there for a reason.

Regards,
Arun Received on Mon Mar 07 2005 - 14:50:25 CST