Re: Why are optimizer hints required?

The House Dawg wrote:
> Are there ways to ensure that when SQL has been put into production
and
> is using indexes that the SQL doesn't eventually degrade into full
> table scans?

See here:
http://www.stanford.edu/dept/itss/docs/oracle/9i/server.920/a96533/outlines.htm (watch the wrapping)

for "plan stability". DAGS on the same string and see the references. This works. If it is too complex, then you can try to snapshot stats and reset them for the relevant objects as described in Jonathan's reply. Interestingly, this is a technique that Jonathan called "cheating" not so long ago. But it works and works good.

If all else fails then there are ways of writing hints without explicit names for anything and yet still make them work. But you need to change how you write SQL. Mostly, you need to associate a table alias with every table in every SQL statement. Then you can use the alias to hint for index usage, rather than explicit index names. This will bypass SOME of the problems. But it is far from perfect. Received on Wed Mar 09 2005 - 00:44:13 CST