Re: Cost based Query Vs Rule based query

I agree with all the points Hans Forbrich made in his reply and would like to add one more: when developing a query which will become part of a "turn-key" application, at development time writing the query to take advantage of the "rule based" optimizer (using hints if the database is in the default "cost" mode) might show a performance advantage. However, after the application is deployed and sitting in production for a period of time, what happens when the underlying data patterns of the table structures change? Three months after deployment, what happens when the query that used an index because it was only returning 5 percent of the data of a table all of a sudden is returning 90% of the data from the table?

An answer to the above question might be to simply rewrite the query. But, what if the query is embedded in a Pro*C function which has multiple dependencies. Upon completion of the code change, a recompile must then take place on all modules which utilized this function...

Hopefully this helps explain a little of the maintenance headaches which go with utilization of the rule-based optimizer. Received on Wed Feb 14 1996 - 00:00:00 CET