Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!uunet!zaphod.mps.ohio-state.edu!magnus.ircc.ohio-state.edu!tut.cis.ohio-state.edu!ucbvax!mtxinu!sybase!jeffl@sybase.Sybase.COM
From: jeffl@sybase.Sybase.COM (Jeff Lichtman)
Newsgroups: comp.databases
Subject: Re: how bad is oracle?
Message-ID: <12376@sybase.sybase.com>
Date: 19 Feb 91 05:31:23 GMT
References: <70683@microsoft.UUCP>
Sender: news@Sybase.COM
Lines: 38

> Note that there are disadvantages to the statistics based optimizers used by
Ingres,
> Sybase, DB2, etc.  A significant amount of time is spent in the optimizer in
> evaluating alternative join strategies (Ingres has a useful optimization 
> default whereby the optimizer will quit if it determines that it has spent 
> longer trying to optimize a query than it would have taken to run the best
plan analyzed in the optimization phase).

If the DBMS has pre-compiled queries, the cost of optimization is paid only
once.  The "advantage" of a syntax-based optimizer disappears in such a case.
In fact, you wouldn't even want to apply Ingres's strategy of stopping the
optimization process if the time spent in optimization is too great, again
because it is a one-time cost - you'd rather pay the price of finding the
absolute best query plan once than the price of running a sub-optimal plan
thousands of times.  In fact, the "give up early" strategy has other
disadvantages - it causes the query plan to become worse when there is
a load on the system, which is when you really want the best query plan.

One other thing about a syntax-based optimizer: one change in the schema
can force you to re-write a bunch of queries.  Suppose you drop an index,
for example, to make certain updates run faster.  Now you may have to change
all of the queries that depend on that index.  This could be a lot of
work, especially if you haven't kept track of which queries use the index.
With a cost-based optimizer, you don't have to change your queries at all -
the optimizer will figure out the best query plan for each query, regardless
of what indexes exist or how the query is written.

This is the real issue, IMHO.  Relational database systems were invented to
improve data independence, that is, to make programs less dependent on the
physical schema than they were with older types of database systems.  Syntax-
based optimizers force the programmer to think about the physical schema
when writing queries, and to change the application when the physical schema
changes.  Cost-based optimizers come much closer to the ideal of separating
the application from the physical schema.
---
Jeff Lichtman at Sybase
{mtxinu,pacbell}!sybase!jeffl  -or- jeffl@sybase.com
"Saints should always be judged guilty until they are proved innocent..."