Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!uunet!elroy.jpl.nasa.gov!decwrl!nsc!voder!blia!nobody
From: nobody@blia.sharebase.com (Nobody at all)
Newsgroups: comp.databases
Subject: Re: how bad is oracle? (really: query optimizers)
Message-ID: <13512@blia.sharebase.com>
Date: 18 Feb 91 21:08:52 GMT
References: <10737@pasteur.Berkeley.EDU> <1991Feb3.182558.14411@oracle.com> <70683@microsoft.UUCP>
Reply-To: mike@woodstock.UUCP (Mike Ubell)
Organization: ShareBase
Lines: 23

In article <70683@microsoft.UUCP> dant@microsoft.UUCP (Dan TYACK) writes:
>If you define an optimizer as some code that chooses a join order
........some comments about systems that optimize......

>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).  Oracle will just go ahead and execute
>a query given to it, without a great deal of overhead.

True enough, but: A good DBMS should beable to optimize at compile time
and find the best query plan without help from the user and then store
the plan so that time critical applications have acceptible performance.
I believe that it takes less time overall and less work from the user
if the optimizer takes the extra time to generate a good plan than
for the user to have to try various orderings of the query and 
then figure out which one runs fastest.  When the database grows and performance
becomes unaceptible because the plan chosen by the user is no longer optimal
the process must be repeated.  The designer of the database may not be
available to redo the optimization and more work will be needed.
If a good optimizer stores the plan, the DBA need only issue a recompile
to get a new (and better) plan when the database changes in size.