Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!thunder.mcrcim.mcgill.edu!snorkelwacker.mit.edu!spool.mu.edu!uunet!samsung!dali.cs.montana.edu!uakari.primate.wisc.edu!zaphod.mps.ohio-state.edu!pacific.mps.ohio-state.edu!linac!att!pacbell.com!pacbell!rtech!ingres!rene From: rene@ingres.com (Account Manager) Newsgroups: comp.databases Subject: Re: how bad is oracle? Message-ID: <1991Feb20.143857.8237@ingres.Ingres.COM> Date: 20 Feb 91 14:38:56 GMT References: <70683@microsoft.UUCP> <12376@sybase.sybase.com> Reply-To: rene@troll.Ingres.COM (Account Manager) Organization: ASK Ingres bv Lines: 42 In article <12376@sybase.sybase.com> jeffl@sybase.Sybase.COM (Jeff Lichtman) writes: >> 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. > some lines left out . . . >Jeff Lichtman at Sybase >{mtxinu,pacbell}!sybase!jeffl -or- jeffl@sybase.com >"Saints should always be judged guilty until they are proved innocent..." Jeff is correct in his statement on cost-based optimizers in general, but the concept of pre-compiled queries has also certain disadvantages, that come very close to those of pre-programmed queries in syntax-based systems. Data size and distribution may vary between queries (unless you would want to pre-compile each query), and they may vary in time. How does a similar system knows when to re-compile ? How does a DBA know for sure that the pre-compiled versions is the fastest ? We (I mean RDBMS suppliers) have often criticized DB2 for doing this, so what solution does Jeff see in this field ? Rene ====* ASK Computer Systems Inc. Ingres Products Division Amsterdam, The Netherlands