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?

After takin a swig o' Arrakan spice grog, "Ryan" <rgaffuri (AT) cox (DOT) net> belched out...:
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.

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.)

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.

How queries perform certainly will vary from DBMS to DBMS. The
differences in locking, paging, and concurrency models will make
tuning strategies vary, although many principles will surely apply
across products.
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www3.sympatico.ca/cbbrowne/languages.html
Rules of the Evil Overlord #131. "I will never place the key to a cell
just out of a prisoner's reach." <http://www.eviloverlord.com/>

Make that DB2 please :-)

Christopher Browne wrote:

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

"Christopher Browne" <cbbrowne (AT) acm (DOT) org> wrote

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"

Oracle cost-based optimiser is a decade old.

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?

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:-)

In article <blg64h$brq8p$1 (AT) ID-125932 (DOT) news.uni-berlin.de>,
cbbrowne (AT) acm (DOT) org says...
It's basically a cost-based optimizer with some internal
pattern-matching rules.

Bob Devine
(one of the folks who wrote it)

"Hrundi V. Bakshi" <Hrundi__V__Bakshi (AT) htmail (DOT) com> wrote

Where did you develop this perverse superstition?

I'd say the difference is not as dramatic as you put it.
I would say Oracle and SQl Server are approximately 3, at most 4 years
behind DB2 in optimizer's capabilities. Maybe it's just 2 years.

Also Oracle leaves a very large footprint, and MS SQL Server cannot be
more reliable than the only flaky operating system it runs on.

That's it. For almost all common tasks, either one of these RDBMS is a
good choice. If you need 24/7, don't consider MS SQL Server.