Re: query optimization in different databases?

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?

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/>