Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!helios!bcm!dimacs.rutgers.edu!seismo!uunet!microsoft!dant
From: dant@microsoft.UUCP (Dan TYACK)
Newsgroups: comp.databases
Subject: Re: how bad is oracle?
Message-ID: <70683@microsoft.UUCP>
Date: 14 Feb 91 21:00:53 GMT
References: <10737@pasteur.Berkeley.EDU> <1991Feb3.182558.14411@oracle.com>
Reply-To: dant@microsoft.UUCP (Dan TYACK)
Organization: Microsoft Corp., Redmond WA
Lines: 43

If you define an optimizer as some code that chooses a join order
and a set of access methods to execute a query, based on statistics on the
cardinality of the relations involved, and the join selectivity of the 
join columns (as does DB2, SQL/DS, DBM, Informix, etc) or based
additionaly on histograms of the distribution of the
data (Ingres), then Oracle doesn't have one.  If you regard an optimizer
as some code that makes efficient use of indexes, based on a join order
specified in the syntax of a retrieval request, then Oracle has
a pretty good one.

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.  Note that given 
equal query execution speed (which is not true, in reality) a properly
hand tuned Oracle query will outperform an equivalent query on a system 
that uses sophisticated optimization.  However, non-tuned queries will
generally perform more evenly when statistics based optimization is used,
especially when complex queries are used, or when the optimizer has the
capability to 'flatten' nested subqueries into equivalent explicit joins.

In article <1991Feb3.182558.14411@oracle.com> jklein@oracle.UUCP (Jonathan Klein) writes:
>In article <10737@pasteur.Berkeley.EDU> mao@eden.Berkeley.EDU (Mike Olson) writes:
>>a couple of days ago i posted a query about oracle to this newsgroup.
>>since i got no response, i thought i would repost with a more provocative
>>subject line.
>>
>>here's the question:  i have heard that oracle does not have a query
>>optimizer.  i find this surprising.  is it true?
>>
>>email or post responses.  i'm eager to hear the answer to this one.
>>
>>					mike olson
>>					postgres research group
>>					uc berkeley
>>					mao@postgres.berkeley.edu
>
>All versions of oracle that I have worked with have had an query optimizer.
>
>Jonathan Klein
>Oracle Corporation