Re: query optimization in different databases?

"Christopher Browne" <cbbrowne_at_acm.org> wrote in message news:blg64h$brq8p$1_at_ID-125932.news.uni-berlin.de...
> After takin a swig o' Arrakan spice grog, "Ryan" <rgaffuri_at_cox.net>
belched out...:
> > how different are the optimizers between the different commercial
> > databases? Will a query written one way from one vendor not run as
> > well under a different vendor?
> >
> > Also, Im familiar with Oracle and I know that physical I/Os are
> > generally not considered much of a problem(its a small piece of the
> > pie). Typical you focus on decreasing logical I/Os. I am reading an
> > academic database book that states that physical I/Os are extremeley
> > important. Is this the case with databases created by other vendors?

We have a saying in India: "Among all the known superstitions of the world, and you do not find in any particular superstition one redeeming feature. They are all alike founded on fables and mythology"

> Different versions of *Oracle* have massive variations in how queries
> are handled.
>
> Older versions used a "rule-based" scheme; more recently, they moved
> to a "cost-based" scheme where how the query is evaluated depends on
> the statistical characteristics of the data in your system.

Oracle cost-based optimiser is a decade old.

> The same is true of DB/2, Informix, and PostgreSQL; the quality of
> query optimization will vary quite a bit between them. DB/2, being
> FAR and away the most mature of the bunch, probably does the best,
> overall. (IBM was doing cost-based query evaluation decades ago.)

That might be true.

Given that optimizer is the most complicated part of the RDBMS implementation (I would say the only nontrivial part), it might be somewhat surprising that optimiser quality don't affect vendors success on the market. Indeed oracle almost owned rdbms market few years ago, and yet most of their customers used rule-based optimizer.

Here is an explanation. "Primitive" rule-based optimizer faired surprisingly well, since rules were able robustly find good access path to the data. If there is an index, optimizer always prefered it to full table scan. In cases where full table scan was more efficient than index, the penalty was never too large. Indeed it is easy to suggest a case where unique indes can is 1000000 times faster than full table scan, but it is very hard to come up with the opposite case where full table scan is meager 10 times faster than index. The often cited motivation for cost-based optimisation is "how do we rank exotic access paths like bitmap indexes and materialized views?". The answer is that they are indeed exotic in OLTP, and why user who finely tuned queries in rule based system should suffer from vendor adding more exotic methods?

> I'm not sure what strategy Microsoft has taken when they rewrote
> Sybase's codebase, whether they are rule-based or cost-based. MySQL
> does rule-based optimization, pointing back to '80s technology.

Microsoft recruited the best people in the field, so the quality of their optimizer must match.

They also publish Data Engineering Bulletin - a respected magazine with many classic articles about SQL optimization. (Ironically, they accept submissions in Latex only, no MsWord:-) Received on Thu Oct 02 2003 - 18:53:11 CEST