Re: Cost Vs. Rules Based Optimizers

In <3g6q7c$ssn$1_at_mhadg.production.compuserve.com> Rich Gaston <75457.515_at_CompuServe.COM> writes:

>

 ....
>
>Developers need to be sync with the DBA's ANALYZE schedule. You
>can spend all night tweaking a query and get it right, only to
>have it "un-tuned" by a re-ANALYZE on the referenced tables the
>next day.
>
 ....
>
>Rich Gaston, CellularOne
>

I'm a little bit confused. If you spent all night (manually) tweaking a query, then what role does/can the cost-based optimizer play? If by tweaking you mean using hints, then why would the update of the statistics alter the solution? (assuming that the hints are still in place)

by the way, since the data distribution in the tables changes, and the values assigned to variables changes (affecting the selectivity of expressions), the "right" query solution also changes with time. You don't usually want to freeze every aspect of the optimizer's solution because you want to give the optimizer freedom to adapt to the current problem at hand rather than forcing it to solve the problem that existed in the lab at the time the query was optimized.

In my experience with Oracle Rdb, which has a facility called 'query outlines' that is similar in some ways to Oracle 7 hints, you want to only supply a few important directives and leave the rest to the optimizer. For example, you might discover that if you specify the first join item, that the optimizer reliably completes the solution optimally.

Very often the DBA will be aware of certain patterns in the data that the optimizer is unaware of. For example, new user IDs might be assigned in ascending order, and therefore a query that accesses both dates and user ids might be able to take advantage of accidental clustering of the data on disk. This relationship is not recorded in the schema anywhere, nor is it recorded in any statistics, yet the DBA is aware of it and can devise an access strategy that takes advantage of it. In cases like this, a pattern in the data can be expected to persist, and judicious use of outlines (or hints) is sensible and leads to better performing queries.

/jim Received on Thu Jan 26 1995 - 03:16:43 CET